Results 1 to 10 of 10

Thread: list values into one cell

  1. #1
    Seeker JYool's Avatar
    Join Date
    Aug 2011
    Location
    Courtenay, BC, Canada
    Posts
    17
    Articles
    0

    list values into one cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have an interesting challenge... what i am trying to accomplish, is a comma separated list of all the project names that an employee has been working on... for instance if b13:b33 has the following values:

    project1
    project1
    project7
    project9
    project1
    project7
    project1
    project1
    etc.

    i want to return to one cell the following: project1/project7/project9

    I did this for the work category using a hidden reference column, returning a numeric value that is stored in a seperate table to find the minimum value, and return the corresoponding text... but the resulting formula is a bunch of if/else results; i capped it at three, so they are either one type, one/two type or multiple type... but i do need a complete list of projects i think, and i don't want a collection of job1, job2, job3...

    i have made another index cell that uses the following formula =INDEX($C$14:$C$33,MATCH(0,COUNTIF($AM$14:AM14,$C$14:$C$33),0)) to create a list that looks like this

    project1
    project7
    project9
    0
    #N/A
    #N/A

    i would again need to nest a bunch of if(isna()) forumlas to get what i need, and that doesn't sound ideal.

    so if anyone has an idea how to deal with this at either stage (the reference column, or the original list) that would be great

    I am trying to expand my knowledge of VB, but am learning from the ground up, as i don't have time for more courses. Can someone point me in the right direction? at this point, I'm pretty good at reverse engineering other people's code to get it to fit my needs, but cannot do anything from scratch.

    thanks in advance!

  2. #2
    You don't say what is in C and AM in your formula, but assuming C13:C33 holds the names of the people, this ARRAY formula gives the projects for Bob

    =IF(ISERROR(SMALL(IF($C$13:$C$33="Bob",ROW($C$13:$C$33)),ROW(A1))),"",INDEX($B$13:$B$33,SMALL(IF($C$13:$C$33="Bob",ROW($C$13:$C$33)),ROW(A1))-ROW($B$13:$B$33)+1))

  3. #3
    Seeker JYool's Avatar
    Join Date
    Aug 2011
    Location
    Courtenay, BC, Canada
    Posts
    17
    Articles
    0
    sorry, i just realized that i should have said the projects are in c13:c33, there is no need for an if function to find which ones match the name, since this whole sheet belongs to one person only. I'm just trying to figure out how to get a list of repeated values in seperate cells... keeping in mind there could be as many projects as days of the month, so i don't realy want to embed if statements...

    and like i said, i have list of only the unique values, if i could get a macro that joins the cells until it finds the value 0, then i have what i need, i just don't know how to write that macro, even badly

    edited to add:
    the AM column is where i placed the formula that gives me a list of unique values, so that's from a15:a33, but most will be #N/A values

    i guess the noob method would be to get rid of the na values with an if statement, then simply join the cells with a space between them, which would result in a long formula and a handful of empty spaces... i was just lloking for the slick way to do it.
    Last edited by JYool; 2011-08-29 at 10:31 PM.

  4. #4
    Seeker JYool's Avatar
    Join Date
    Aug 2011
    Location
    Courtenay, BC, Canada
    Posts
    17
    Articles
    0
    this is a clean example of what I'm trying to dosample problem.xlsx

    what i want in a cell is Project 1/Project 7/Project 9

    allowing for the number of projects to be dynamic

  5. #5
    Seeker JYool's Avatar
    Join Date
    Aug 2011
    Location
    Courtenay, BC, Canada
    Posts
    17
    Articles
    0
    ok I'm about 17 steps closer... i used someone else's code and a little brain power to get this far:

    Sub proj_join()
    trans = Range("D24").Value

    trans = Application.Transpose(trans)
    strTemp = Join(trans, "/")
    ActiveCell.Value = strTemp
    End Sub


    all i need now, is to figure out how to make the range dynamic: it should start (in this workbook) at d2, and go to the cell before 0... i could do it in a forumla, but vb is still new to me.

    plesae feel free to steer me on the right path if this is not the right idea

  6. #6
    A better version of your formula is

    =IF(ISNUMBER(MATCH(0,COUNTIF($D$1:$D1,$B$1:$B$20&""),0)),INDEX($B$1:$B20,MATCH(0,COUNTIF($D$1:$D1,$B$1:$B$20&""),0)),"")
    Last edited by Bob Phillips; 2011-08-30 at 08:52 AM.

  7. #7
    Try this code

    Code:
    Sub proj_join()
    Const FORMULA_UNIQUES As String = _
        "SUMPRODUCT((B1:B20<>"""")/COUNTIF(B1:B20,B1:B20&""""))"
    Dim numrows As Long
    Dim trans As Variant
    Dim strTemp As String
    
        numrows = Application.Evaluate(FORMULA_UNIQUES)
        trans = Application.Transpose(Range("D2").Resize(numrows))
        
        strTemp = Join(trans, "/")
        ActiveCell.Value = strTemp
    End Sub

  8. #8
    Seeker JYool's Avatar
    Join Date
    Aug 2011
    Location
    Courtenay, BC, Canada
    Posts
    17
    Articles
    0
    hooray! that's exactly what i was trying to accomplish. I can't wait to take a course on VB and figure it out once and for all

  9. #9
    Seeker JYool's Avatar
    Join Date
    Aug 2011
    Location
    Courtenay, BC, Canada
    Posts
    17
    Articles
    0
    two things still.
    this code doesn't work if there is only one project... which is going to be the most frequen result,

    second, how to i assign a cell instead of using the current activecell to hold the final .value (I'd like to call on the macro every time the list of jobs changes so i don't know if i have to tell it first to select aj12 (the actual spot i want this result)

    thanks for all the help! I'm amazed how much people are willing to offer to people they don't know...

  10. #10
    Code:
    Sub proj_join()
    Const FORMULA_UNIQUES As String = _
        "SUMPRODUCT((B1:B20<>"""")/COUNTIF(B1:B20,B1:B20&""""))"
    Dim numrows As Long
    Dim trans As Variant
    Dim strTemp As String
    
        numrows = Application.Evaluate(FORMULA_UNIQUES)
        trans = Application.Transpose(Range("D2").Resize(numrows))
        
        If numrows = 1 Then
             
            strTemp = trans
        Else
            
            strTemp = Join(trans, "/")
        End If
        Range("AJ12").Value = strTemp
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •