Tricky Excel Code

This is a basic sample of what I have in Colums A and B:

123 a
123 b
654 d
654 a
654 r
789 b
789 q
963 s

This is the format I would want two news columns in:

123 a,b
654 d,a,r
789 b,q
963 s

Basically I am trying to merge all the identical A cells while combining the adjacent data into a single, new cell. There are thousands of these, but this is the basic idea.

Any thoughts. Thanks.

What short code can I type into the excel equation bar to take only:

123
123
654
654
654
789
789
963

And make it (in some other column):

123
654
789
963

You can do the above by using advanced filter. Highlight the cells, select Data > Filter > Advanced Filter, select copy to new location, and check the unique records box.

Jason

Great.

Any thoughts on the next (original) part? So now I have:

123 a
123 b
654 d
654 a
654 r
789 b
789 q
963 s

AND:

123
654
789
963

How can I get to:

123 a,b
654 d,a,r
789 b,q
963 s

You could construct a matrix in which you lookup the matches in column b to the unique values in column a:

transpose the unique set in C1:G1:
{=transpose(array of unique dataset)}

match the values in column b to that in a:
C2=IF(B2=$A$2;C2;"") and copy down
D2=IF(B2=$A$3;C2;"") etc.

Use concatenate() to show the values and text in the format you like

I think there should be a more efficient way, but this will work anyway...
HTH

Peter