# Thread: list values into one cell

1. ## list values into one cell

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 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.  Reply With Quote

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))  Reply With Quote

3. 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

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.  Reply With Quote

4. 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  Reply With Quote

5. 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("D2 4").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  Reply With Quote

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)),"")  Reply With Quote

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```  Reply With Quote

8. 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  Reply With Quote

9. 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...  Reply With Quote

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```  Reply With Quote

#### Posting Permissions

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