![]() |
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 |
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 |
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