vlookup, same row
 

I need to look up all duplicate account# (Column B)and merge all comments
(column E) into one row. I tried to use the Concatenate formula, but I could
not get it to work correctly. I was pulling comments from the row below.
Any suggestions would be great thanks.



Example:
DED222562 please provide ppd information 9/06/07 AB
DED222562 Customer will not pay 11/13/07 fer
DED222562 needed infm as to why was it ppd and a copy of debit memo

vlookup, same row
 

Assume your data looks like this:

Account
AH406 Cva
WP347 Geo
ME880 Ypc
ZB252 Uls
AH406 Bvm
WP347 Nie
KJ283 Msf
AH406 Bax
ZI212 Enp
YL244 Gem
AH406 Diu
WP347 Kij
ME880 Kgs
Comnt

Account=$A$2:$A$14
Comnt=$B$1:$B$14
cell C2 =IF(COUNTIF($A$2:$A$14,A2)1,1,0)=1
leave C1 blank
Filter accounts for more than 1 comment
Data Filter Advanced Filter
Copy to another location unique records only
List Range: $A$2:$A$14
Criteria: $C$1:$C$2
Copy to: $D$1
The filtered results at D1 will look like this:

Account
AH406
WP347
ME880

At E2 enter this array formula
=IF(COLUMNS($E$2:E$2)COUNTIF(Account,$D2),"",
INDEX(Comnt,SMALL(IF(Account=$D2,ROW(Account)),
COLUMNS($E$2:E$2))))
Click and drag the formula down 3 and to the right 4 cells.
The results will look like this:

Account
AH406 Cva Bvm Bax Diu
WP347 Geo Nie Kij
ME880 Ypc Kgs

Concatenate if desired.