Want to Find Common Keywords in A Larger Column Using Another Column

[cje1111]

Hello All,

I have a very large list of keywords. Out of this list I want to find only the cells that have the keywords in the order of my smaller list of main keywords.

For instance, I have a small list with the main keywords such as:

pill prenatal
pills prenatal
prenatal supplement
prenatal supplements
prenatal vitamin
prenatal vitamins
prenatal multivitamin
prenatal multivitamins

My larger list has many of those keywords but not in the correct order such as:

trusted vitamins prenatal why pills
trusted vitamins prenatal why prescribed
trusted vitamins prenatal why regimen
trusted vitamins prenatal why routine
trusted vitamins prenatal why take
trusted vitamins prenatal why taking
trusted vitamins prenatal why written
trusted vitamins prenatal why chewable
trusted vitamins prenatal why complete
trusted vitamins prenatal why fertility
trusted vitamins prenatal why gummy
trusted vitamins prenatal why natural
trusted vitamins prenatal why prescription
trusted vitamins prenatal why small
trusted vitamins prenatal why women
trusted vitamins prenatal why womens
trusted vitamins prenatal why safe
trusted vitamins prenatal why ingredients
safe taking prenatal pills pills
safe taking prenatal pills prescribed
safe taking prenatal pills regimen
safe taking prenatal pills routine
safe taking prenatal pills take
safe taking prenatal pills taking
safe taking prenatal pills written
safe taking prenatal pills chewable
safe taking prenatal pills complete
safe taking prenatal pills fertility
safe taking prenatal pills gummy
safe taking prenatal pills natural
safe taking prenatal pills prescription
safe taking prenatal pills small
safe taking prenatal pills women
safe taking prenatal pills womens
safe taking prenatal pills safe


I would basically like excel do show me which cells in my large list have the first list but (very important) in the first list's words order. For example prenatal vitamins, not vitamins prenatal. So if any of the cells from my large list of keywords have any of the keyword phrases, I would like to have excel let me know this. Again not just that those words are in the list but in the correct order (like "prenatal vitamin".

I will upload an example with the main list and the much larger list.

I know excel ok but am not a super advanced pro. Thanks!

[icestationzbra]

see if this works:

[cje1111]

Thanks for the quick reply! Is it that if there's a zero those keyword phrases aren't in the main list's cells in the correct order and that's a ) but if they are its a 1?

I tried this out on the larger keyword list but its showing:

best prenatal vitamins with iron 0
best prenatal vitamins without iron 0
best vitamins for pregnant women 0
can i take prenatal vitamins 0
chewable prenatal vitamins with dha 1
chewable prenatal vitamins with iron 1
different types of prenatal vitamins 0
easy to swallow prenatal vitamins 0
folic acid in prenatal vitamins 0
folic acid vs prenatal vitamins 0

In my main keyword list I have the phrase "prenatal vitamins" but as you can see from above its showing a 1 as a result in some cells and a ) in others.

To clarify, what I like is for Excel to look in my Main Keyword List and only let me know the cells that have those keyword phrases in them in their particular order. With the example above and the keyword phrase "prenatal vitamins" i would be only the ones in bold that I would keep because they contained the phrase "prenatal vitamins".


best prenatal vitamins with iron
best prenatal vitamins without iron
best vitamins for pregnant women
can i take prenatal vitamins
chewable prenatal vitamins with dha
chewable prenatal vitamins with iron
different types of prenatal vitamins
easy to swallow prenatal vitamins
folic acid in prenatal vitamins
folic acid vs prenatal vitamins

ps It would also be cool to be able to highlight the ones I want to keep so then I can sort and filter quickly by color.

Thanks again for your expertise!

[icestationzbra]

since you have your data on two different sheets, it is not possible to colour cells using Conditional Formatting the way you desire. take a look at this:

[cje1111]

Thanks for that!

I think the way you have the spreadsheet set up, to show True for phrases in my large keyword list that match any of the phrases in my main keyword list and any others to say false.

If you can walk me through how to set this up on an existing spreadsheet as I'm not sure where the formulas go and in what order. I tried to copy the formulas to the two sheet of the workbook I have but it didn't work like you have it. Thanks!

[icestationzbra]

upload a sample file with your data in it, we can help you set it up.

[cje1111]

Here you are...

[cje1111]

Here's a better example spreadsheet...

Example 3.xlsx

[icestationzbra]

here you go...

[cje1111]

icestationzbra,

The formulas seem to work perfect. Thank you!

So please correct me if I'm wrong but to use this I'd create a spread sheet with a sheet named Large Keyword List another names Main Keyword List.
Then I'd take the formula from column B and copy it down. Then I'd take the formula on the second sheet in column B and copy it down and it should work?
I tried to copy the formula down to all cells by clicking on the lower right hand corner of the cell. This usually works but did not for either formula. Is there another way to quickly copy the formula down as my spreadsheets sometimes have over 100K keywords and it would take forever to drag it down.
Also, when I copied this formula to my existing spreadsheet it worked but didn't highlight the cells, not sure if I'm doing something wrong.

Thanks again for all your help and patience!

[icestationzbra]

instead of copying the formulae over from my spreadsheet onto your workbook, why don't you trying copying your data from your workbook onto my spreadsheet in appropriate places? there are Conditional Formatting formuale in place in my spreadsheet, that is what is highlighting the cells. in my opinion, it is the easiest way to go.'

a general note about auto-filling formula down an entire column:

let's say you have data in column A from row 1 to 1000, where A1 is header and A2 onwards reside your data. if you enter a formula in the immediate adjacent column B, in cell B2, and double-click on the fill-handle, the formula will auto-fill all the way down from B2 to B1000. if you leave a columnar gap between data column and formula column, then this feature will not work.

[cje1111]

Excellent! Thank you!

[cje1111]

I noticed that since I have very large amounts of keywords (sometimes over 100K) the filtering does not work that well. For instance I tried to filter based on "True" but when I did out of the 100K it said about 56K were marked as "True" but the list still went all the way down to 100K, marked as "True" after filtering. Any ideas on how to filter these larger amounts? Thanks!

[icestationzbra]

i am unable to understand what you are trying to say.

just for the heck of it, check if there are blank rows (cells) along your Keywords column.

[cje1111]

Thanks! I checked and there aren't any blank rows. But what I'm trying to do is to sort or filter by whether column B is True or False. But when I go to do this it doesn't do anything. It would be a pain to have to go through 100K rows and manually delete all the false ones. Any ideas?

[cje1111]

I've been trying to sort but still not working. Basically, when I go to sort by True or False or by color I run into issues of it not sorting correctly after the Sorting is applied.

I did Sort & Filter> Custom Sort> Expand Selection > Sort by Column B > Sort on Values ( I also tried colors of cells, same result ) > Order True, False > OK.

Once I applied the filter it sorted, it does one of two things: 1. Nothing 2. Going back and forth between True and False but I noticed the True and False didn't match up with the Main Keyword List. Also, the Main Keyword List changed from True & False to only False.

[icestationzbra]

my last ditch attempt to assist would be to ask you for the file you are struggling with. if you are fine with sharing the file with me, send me a PM and i will share my email address with you. if you send me the file, i will see what i can do.