Wild card match and then count

[LITTLE PETE]

Afternoon

I have a data set (50K+) with various product codes such as 618B TPR, 618B
PGR, 13 VCR etc.

Firstly I want to complete a count exercise using the above codes to see how
often they match themselves within a description of the item. I somehow
need to use wild cards because it will be impossible to trim the description
down to just the production code. Happy for the solution to work on 80% of
the data.

Where it is a 1:1 match I will then aling these data records with each using
a vlookup function.

Thanks

 

[Roger Govier]

Hi Pete

I'm not sure I am fully understanding your requirement, but one way (if
there aren't too many product codes) would be to insert 2 new rows above
your data.
Assuming your data is in column A only.
In B2 enter the first product code, C2 the next etc for each of the codes
you want to find.
In B3 enter
=--(ISNUMBER(FIND(B$2,$A3)))
Copy down for the 50,000 rows of data.
Copy the whole of the column across for as many codes as you are looking
for.

In B1 enter
=SUBTOTAL(9,B3:B50000)
Copy across as far as required.
You will have a count in B1 onward for your matches for each code.
Highlight row 2>Data>Autofilter and use the dropdown to select 1 for any of
the columns, and you will have a filtered list of the rows where matches
occur.

 

[Rick Rothstein \(MVP - VB\)]

If I understand your question first question correctly, consider the
following. If Column A contains your product codes and Column B contains
your descriptions, the following formula will tell you how many times the
product code is repeated in the description.

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,MID(B1,ROW(INDIRECT("1:"&LEN(B1))),LEN(A1)))))

Here I have assumed your data starts in Row 1. If it start in another row,
simply replace the 1's that are directly part of a cell reference to the
number of that row (in other word, change all the 1's EXCEPT for the 1
following the quote mark).

Rick