Lookup Data 1

[rss01]

Hi, I have a excel sheet that has 2 columns. column A is a part number and column B has a string of text. there can be numerous entries in column A of one value.

Column A Column B
1 1111111
1 2222222
1 3333333
1 4444444
2 1111111
2 2222222
3 1111111
4 1111111
4 2222222
4 3333333

What i want is something like this

Column A Column B
1 1111111,2222222,3333333,4444444
2 1111111,2222222
3 1111111
4 1111111,2222222,3333333

Any ideas how to accomplish this?

Thanks in advance.

 

[melaniecarr23]

I don't know if you can do it in excel or not, but can't you use a loop and establish a variable which concatenates the values for each number in the first column?

Maybe use a macro?

 

[Zathras]

Assuming your (sample) data are in A1 thru B11 with headings in row A, put these formulas in C2 and D2 and then copy down:
[tt]
C2: =IF(A2=A1,C1&","&B2,B2)
D2: =IF(A2=A3,0,1)
[/tt]
Then copy and paste special values to replace the formulas with hard data.

Sort the data on columns D and A

Delete all rows with a zero in column D.

Delete columns C and D.

Voila!

 

[rss01]

Thanks Zathras, that worked like a charm.

 

[sawedoff]

To build on that theme (which was brilliant, Zathras) Add a column in between B and C, and in that column put
=IF(A2=A3,A2,"")

Then you can do a lookup on another sheet, to bring the number, and the combined text into the summary page. I used this formula:
=VLOOKUP(A2,Sheet1!$C$2:$D$9,2,FALSE)

If necessary, you can insert multiple columns between B and C to move the "dirty work" out of sight, and leave it there for future use.

Sawedoff