Horizontal Data Grouping

[derekstewart]

I have a dataset in Excel with the follwing pattern where there is one primary number and several related numbers:

Prim Secondary
13 10259
13 44589
21 66514
21 44577
21 22187

I would like this info displayed horizontally, seperated by a comma in the same cell like this:

13 10259,44589
21 66514,44577,22187

The count of secondary numbers varies from 1 to 11. I did an IF statement that does it for only a set number of secondary but am not sure how to loop it while a2 = a3 and then do it again for the next number in the column. Any thoughts? Thanks. Derek

 

[Zathras]

This can be done with a macro, of course. But, if you prefer, you don't need to go that way. The following formulas and procedure will do the job for you:

1. Assuming your data are in columns A and B (with column headers), put the following in columns C and D:
[tt]
C1: Prim
D1: Secondary
C2: =IF(A2=A3,"",A2)
D2: =IF(A2=A1,D1&","&B2,B2)
[/tt]
2. Copy the formulas from C22 down as far as your data goes.
3. Copy and paste/special/values from columns C to another sheet (or other columns in the same sheet).
4. Sort the data on the Prim column.
5. Delete rows where there is no value in the Prim column, and voila.

 

[derekstewart]

Thanks. It works great Derek