formula to find the no. of cells in a column having 2 unique words

[srikant agrawal]

I have a column 'A' with 10,000 long list. Each cell has a string, actually a product name. We have another column 'B' which has let's say 1000 keywords of 2 to 4 words combination string. We want to find how many times each of these keywords have appeared in Column 'A'. The order of words in the keyword in not important. They don't have to be case sensitive.

Column A                                       Column B                              Search Result
Bio Care Wine & Beer Scrub            Ayur Cream                           1
Domex Zero Stain Toilet Cleaner     Bambino Roasted                   2
Bajaj NOMARKS Face Pack              Bambino Xpress                     2
Henko Stain Detergent Powder        Britannia Cake                       2
Ceres Mango Juice                          Britannia Biscuits                   2
Bambi Fabric Softener                     Henko Detergent Powder        1
Bambino Roasted Xpress Vermicelli
Bambino Xpress Pasta
Haldiram's Moong Dal
Bambino with Roasted Gram Powder
Basic Ayurveda Jamun Herbal Mix with Tulsi
Haldiram's Nut Cracker
Bachun Oyster Sauce
Ayam Brand Spread In Mayonnaise
Ayur Herbals Cream with Aloe Vera
BRITANNIA Pineapple Plunge Cake
BRITANNIA  Cracker Simply Lite Biscuit
BRITANNIA Orange Bites Cake
BRITANNIA Chocolate Sandwich Biscuits

Since we have thousands of such keywords in column 'B' can we have formula which we can drag across the entire column to calculate? Even if someone can come up with a formula for defined number of words e.g. 2 or 3 it would be great.

 

[NARAYANK991]

Hi ,

Please upload your workbook with enough data in it.

Narayan

 

[NARAYANK991]

Hi ,

One problem is that your list of keywords in column B is itself a combination of multiple words , which may or may not occur in the list in column A in the same order and together.

Thus , one of your cells in column B contains the following :

Ayur Cream

while one of the entries in column A is the following :

Ayur Herbals Cream with Aloe Vera

If you want the two to be matched , then it is possible only if the combination is column B is separated into its individual words Ayur and Cream , so that these two words can be individually searched for in the list in column A.

Narayan

 

[srikant agrawal]

Hi Narayan,

Thank you so much for your quick response. I have uploaded a sample file in the original question so that it is easier to work. It has sample data of 2000 products.
Regarding your queries, you are right about everything. It was a simple search I would not have posted here. I have gone through searching of multiple words when occurring together. However, our problem is, as originally stated, how to find the combination of these 2 words, whether they are together or not, in a single cell. Once found we need to increase the count.
I have already found the longer route solution. It is same what you have suggested.
But I want a single formula to drag across the length of the column.
My hunch says it is possible. But I don't know how!
That's why I am here.

 

[xlstime]

Hey Srikant,

try below formula with Ctrl+Shift+Enter (array )

c2 = =SUM(COUNTIF($A$2:$A$2000,"*"&TRIM(MID(SUBSTITUTE(" "&B2," ",REPT(" ",164)),164*ROW(INDIRECT("1:"&(LEN(" "&B2)-LEN(SUBSTITUTE(" "&B2," ",""))))),164))&"*"))

Hi Narayan,

Thank you so much for your quick response. I have uploaded a sample file in the original question so that it is easier to work. It has sample data of 2000 products.
Regarding your queries, you are right about everything. It was a simple search I would not have posted here. I have gone through searching of multiple words when occurring together. However, our problem is, as originally stated, how to find the combination of these 2 words, whether they are together or not, in a single cell. Once found we need to increase the count.
I have already found the longer route solution. It is same what you have suggested.
But I want a single formula to drag across the length of the column.
My hunch says it is possible. But I don't know how!
That's why I am here.

 

[shrivallabha]

In cell C2 put below formula, copy down and test. It can check upto 4 words at the moment in column B. It will be slow.
=SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(TRIM(B2)&" @ @ @ @"," ",REPT(" ",99)),{1,100,199,298},99)),$A$2:$A$2000)),{1;1;1;1})=(LEN(TRIM(B2))-LEN(SUBSTITUTE(TRIM(B2)," ",""))+1)))