VBA to record multiple string search occurence in range

Hi All

I need guidance as to which procedure to use for the following problem...
I have uploaded a sample file consisting of:

Sheets("Customer Listing")
Sheets("Customer Sales")
Sheets("Product Breakdown Description")
Sheets("Product Breakdown")

Sheets("Product Breakdown Description") contains the following headers with each having there own search criteria in rows below.
Alcohol,Chillbev,Energy,Generic,Penbev,Craft,Other

I need to search Sheets("Customer Sales").Range("A")
For the "ACTIVE" Customers from Sheets("Customer Listing").Range("A")
And Find all occurrences of Sheets("Product Breakdown Description") within
Sheets("Customer Sales").Range("B")
And record the occurrences in Sheets("Product Breakdown") under the appropriate headers.

My mock up depicts in Sheets("Customer Sales") all products highlighted(for reference only)with there particular color dependent on the type of Header....
and I've merged column C to show same product occurrences that count as 1 only.

So basically if a product is listed in the particular clients sales which falls under the particular Headers search criteria then the count is 1, if it is a duplicate then the count is still 1.


Sheets("Product Breakdown") currently has the required result.
Any guidance will be appreciated.
My code thus far only populates the active client list in Sheets("Product Breakdown")

Please Login or Register  to view this content.

..................Deleted.....................

This code works but can be simplified I'm sure....

Please Login or Register  to view this content.

EDITED:

Have managed to come up with the above code that fulfills my requirement....

Thanks Leo for your response....See amended code in post 3 which works.....
Could be simplified I'm sure...

Hi LeoTaxi

Absolutely love the simplified code....Thanks so much...
Noticed your notes...my keywords are gonna cause errors of course.
The SP is the keyword for this product range...SP LIMONATA LATTINA X24, SP MELOGRANO X24 ETC
Any workaround for this or must I get my client to change there product descriptions.

I just can't wrap my head around it....What I want to do is put All the SP Flavours in an array and if any of them are found in sales then the count must be 1 only.

That will solve the problem, however, i just realized that there are a few other discrepancies with this keyword method....Am going to open a new post with a small example to see if we can solve this....Thanks again for your time Leo.