Find all cells in "A:A that contain "B\$1\$" and list all values starting in " B\$2\$"

dwrgt3

New Member
I have a list, in Column A, of 900 customer codes. Examples are AFP, AU3, NKD, YM2, JWW etc... They could be anything, but are always either 2 or 3 characters.

In Column B and Column C I have those codes broken down into the "First two characters"(column B) and then the "Last two characters"(Column C)

I am creating a list of exceptions so to speak. I need to find all the codes in Columa that contain the value in "B1". Then take all of the values that match

A B C

So the first row looks like this: ABN AB BN

I need to create a list stating in Column D that would show all the codes in Column A that contain "AB" and then "BN" and list them in ABN's row. I need to do this for each of 900 codes, so in the end each code has a list next to it of all the other codes that have partial matches within them,

I have been messing with this for weeks. I have been manually using Filters, which is working, but it is very time consuming. and then i also have to copy, transpose, paste, then mark all the "BN's" as complete. Its insane.

I would love a macro to do this, or a formula. I'm not asking for you to do the work for me, simply point me in the right direction. But if you have something built that does this i will gladly take it and modify it to my needs, but any help would be greatly appreciated it. I am so lost...Thank you!

Rick Rothstein

MrExcel MVP
Assuming your list starts at cell A2, it sounds like you are asking for these formulas...

B2: =LEFT(A2,2)

C2: =RIGHT(A2,2)

dwrgt3

New Member
Assuming your list starts at cell A2, it sounds like you are asking for these formulas...

B2: =LEFT(A2,2)

C2: =RIGHT(A2,2)

Hi Rick,
This formula creates the first two and last two in different cells, which i have already done. I need something that will take GU and search column a for anything containing GU and place all of those results in GUS's row starting in column D. Does that make sense? I hope i have explained it correctly.

1,082,250
Messages
5,364,020
Members
400,774
Latest member
Goldi paul

This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...