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

dwrgt3

New Member
Joined
Nov 19, 2014
Messages
4
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
Joined
Apr 18, 2011
Messages
35,507
Office Version
2010
Platform
Windows
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
Joined
Nov 19, 2014
Messages
4
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.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top