ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Difficult look-up request (https://www.excelbanter.com/excel-discussion-misc-queries/71902-difficult-look-up-request.html)

natejohns7

Difficult look-up request
 

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


--
natejohns7
------------------------------------------------------------------------
natejohns7's Profile: http://www.excelforum.com/member.php...o&userid=31597
View this thread: http://www.excelforum.com/showthread...hreadid=512898


Michael

Difficult look-up request
 
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


"natejohns7" wrote:


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


--
natejohns7
------------------------------------------------------------------------
natejohns7's Profile: http://www.excelforum.com/member.php...o&userid=31597
View this thread: http://www.excelforum.com/showthread...hreadid=512898



Michael

Difficult look-up request
 
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.
--
Sincerely, Michael Colvin


"natejohns7" wrote:


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


--
natejohns7
------------------------------------------------------------------------
natejohns7's Profile: http://www.excelforum.com/member.php...o&userid=31597
View this thread: http://www.excelforum.com/showthread...hreadid=512898




All times are GMT +1. The time now is 08:37 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com