Difficult look-up request

N

natejohns7

Column A contains serial numbers most of them are duplicated.
Column B contains specific text.

Here is what I need to do.

A B

1 L
2 N
3 P
2 S
4 D
5 F
2 R

I want to combine the text from column B for every instance Column A
equals a certain number. For example If look up (2) in column A...I
want it to return (NSR).
 
M

Michael

Hi natejohns. There is a way you can do this that is permanent. If you want
to keep the original data, you should copy the sheet and use that. You need
to use two helper columns and use labels in row 1 so that your data starts in
row 2. With the serial number in column A and the text in column B, In C2
type: =B2. In C3 type: =if(A3=A2,B3&C2,B3) and copy down the length of your
data. You'll see in C5, next to the final 2 that you get NSR. Copy column C
and paste special-values on itself. In D2 type:=if(A2=A3,"D",""). This will
put a D in all the duplicate cells except the last one, which you want to
save. Finally, auto filter Column D, select D and then delete all those
rows. HTH
Sincerely, Michael Colvin
 
M

Michael

Sorry natejohns. I left out a step. After putting labels in row 1, sort
ascending on column A first to get all your duplicate serial numbers together.
 
Top