Concatenate problem
Lots of good suggestions but here is one that uses just standard worksheet
functions:
In column C, assuming your IDs are sorted (put this in C1 and copy down)
=IF(ROW()=1,B2,IF(OFFSET(A2,-1,0)=A2,OFFSET(C2,-1,0)&B2,B2))
This will "build" the concatenated string in each consecutive cell where the
ID is equal - if you can live with that and extract the last line it might do
for you. If you need JUST the final result then enter a new column D beside
the one created above and in D1 enter (then copy down) the formula:
=IF(A1=A2,"",C1)
This will leave the cells in D blank until you reach the last line having a
particular ID, where it will pick up the concatenated value from C.
No coding needed!
"tmwilkin" wrote:
Hello,
I'm working a file with two columns of data. The first column is an ID
number, the second is a description keyword. An example is shown below:
388643 WSPHD170440 PE170IP4 417IPEB
388643 LIPE170IP4 IN004PE170IP4
388643 ZZFAB
388643 VVFL
388643 VVNW
766220 IN004!PE110IP5
766220 VVWV
I want to concatenate the keywords for each unique ID into one row/cell (col
B). Can anyone assist? I can work with any function that generates
duplicate rows as I could strip them out later.
Thanks in advance,
Todd
|