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.
|