PDA

View Full Version : list values into one cell

JYool
2011-08-29, 06:41 PM
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.

Bob Phillips
2011-08-29, 11:10 PM
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))

JYool
2011-08-29, 11:27 PM
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.

JYool
2011-08-29, 11:48 PM
this is a clean example of what I'm trying to do266

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

allowing for the number of projects to be dynamic

JYool
2011-08-30, 12:05 AM
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:D4").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

Bob Phillips
2011-08-30, 09:45 AM
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)),"")

Bob Phillips
2011-08-30, 09:52 AM
Try this 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

JYool
2011-08-30, 10:09 PM
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

JYool
2011-08-30, 10:32 PM
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...

Bob Phillips
2011-08-30, 10:43 PM
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