Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |