View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg Herbert Seidenberg is offline
external usenet poster
 
Posts: 1,180
Default 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.