Auto color fill cell of a LARGE list of words

[cryptojunkie]

I have a spread sheet of about 2000 words. I also have a list of about the same amount of words. I need a way to automatically check the list of 2000 words against the spreadsheet and when theres a match it fills the cell a specific color...thanks

[dosydos]

In theory use conditional formatting with a countif() function as the criteria
instead of explaining steps please see the below link:


https://www.exceldemy.com/excel-high...t-from-a-list/

[cryptojunkie]

Thanks for your response. I checked out the site...great information however I cant seem to get it to work. I dont know anything about excel so im obviously doing something wrong...Thanks anyways!

[JeteMc]

Hello cryptojunkie and Welcome to Excel Forum.
We might be in a better position to help if we could see a sample Excel file illustrating the issue.
Information on attaching an .xlsx file are given in the banner at the top of the page.
Please be sure to manually include the results desired so that we have something with which to compare our proposed formulas/code.
Let us know if you have any questions.

[cryptojunkie]

So when you say sample should I just take each file im working with and delete half the words? I dont have anything to show the desired results just what I was trying to do which is take spreadsheet filled with words and compare to document filled with words and when there is a match it fills the cell a certain color

[JeteMc]

I would suggest providing a spreadsheet with 10 words and a list with 10 words.
Make sure that there is at least one matched word and at least one unmatched word.
Manually fill the cells you want to have automatically filled by Excel.
Let us know if you have any questions.

[cryptojunkie]

OK Thank you for your response I really appreciate it. I have attached excel and doc files with just a few words. Again just in case im not clear on what im trying to do since I dont know the lingo. I want to compare the list in the doc to the spreadsheet and once there is a match it changes the cell to whatever color of my choice and it leaves unmatched as is. Thanks again this is so helpful I've spent hours trying to figure this out and I've gotten nowhere.

[JeteMc]

Didn't realize that the list was in a docx file, so this isn't going to be an elegant suggestion.
1. Copy/Paste the list into a cell (example cell A1 on Sheet2)
2. Use the following formula as a conditional formatting rule: =SEARCH(A1,Sheet2!A$1)
Note that column B on Sheet1 is not needed, just used to aid in understanding how the conditional formatting rule works.
Let us know if you have any questions.

[cryptojunkie]

Ok thanks for the info on putting the list into the spreadsheet I figured that was the case but wasnt completely sure. The actual spreadsheet is Column A through AS and Row 1 through 45 So if i create a sheet with the 2000 word list and I have my full spreadsheet How would I have it cover all the columns? Thats where I mostly get stuck at. Also I have a lot of sheets. Does it matter if the sheet is directly next to each other? Thanks so much

[cryptojunkie]

So im creating a sheet with the 2000 words. I copied them all from the Doc of course then i click in cell a1 and paste and it paste ALL of them into that cell horizontally... is this a problem??

[JeteMc]

Should not be a problem.
The only change should be that the "Applies to" for the conditional formatting formula should be: =$A$1:$AS$45
If you have a problem, it will be easier to troubleshoot if you will upload the file.
Note that the forum limit for .xlsx files is 1,000 KB, however files saved as .xlsb are usually smaller and if that fails then you might try uploading as a .zip file.
Let us know if you have any questions.

[cryptojunkie]

I feel like such a fool I am sorry. I tried to interpret what you were telling me I just dont understand this.Thanks again

[AliGW]

Is this for GoogleSheets?

Administrative Note:

Welcome to the forum.

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

[JeteMc]

Not to worry, I forgot to take into account that since the formula would cover multiple columns it would need to be modified to read: =SEARCH(A1,BIP!$A$1)
The reason being so that the reference to cell A1 on the BIP sheet would stay locked for both the column and row.
By the way, it is my understanding that the SEARCH function works the same in Excel as in google sheets, however I haven't tested with sheets.
Let us know if you have any questions.

[cryptojunkie]

Yes google docs....perhaps they are different or I did something wrong. I downloaded the file. then I coped sheet one (PB) to a blank google sheet then created a sheet 2 for bip all the same exact names. Went to conditional formatting applied to range is A1:AS45 then custom formula is SEARCH(A1,$BIP.$A$1) and it didnt work. I put the = and it says invalid format so your questioning of the search might be accurate...Also sorry about the google docs I just assumed a spreadsheet was a spreadsheet...

[cryptojunkie]

Updated...thanks!

[JeteMc]

SEARCH(A1,$BIP.$A$1) doesn't look right, specifically the $ before BIP and the period after BIP
For Excel it would be written =SEARCH(A1,BIP!$A$1)
This appears to also be the syntax for google.
Let us know if you have any questions.

[cryptojunkie]

Thanks again for your help...However I believe I am a lost cause. First I do not know how =SEARCH(A1,BIP!$A$1) got changed sorry about that. But I tried again using that and it didn't work. If i have the equals sign in it says invalid formula. If I take it out then nothing happens> Not sure what to do from here but thanks for your help I do appreciate it.

[JeteMc]

You may want to request that a moderator move this thread to the "For Other Platforms(Mac, Google Docs, Mobile OS etc)" forum, where perhaps a contributor who has specific knowledge of google docs can help.

[cryptojunkie]

Great idea. Thanks again. Sorry for not realizing the spreadsheet wouldnt carry over the same from one program to another

[cryptojunkie]

Hello, I have made a mistake on where I posted my question. May you please move this to "For Other Platforms(Mac, Google Docs, Mobile OS etc)" Thank you kindly

Ooops sorry I thought I was replying directly to the mod and I cant delete the post

[AliGW]

The thread has been moved.

[cryptojunkie]

Thank you kindly

[janmorris]

if i understand correctly, you want to colour the cells in a list where the words exist on another list.

to do this, use this formula:

Please Login or Register  to view this content.

here is a sample Google Sheets file that you can make a copy of:
https://docs.google.com/spreadsheets...gbQ/edit#gid=0

[cryptojunkie]

Thanks for the reply...Im such an idiot so please forgive me...The original sample sheet I submitted was just that of a few words which was recommended the problem is the two lists are upwards to 2000 words each. The main sheet is 45x45. The other sheet the entire list of words goes into A1 only when I paste it from a document. I went to unconditional formatting on the sample sheet you updated but for some reason I didnt see anything. In a later post I submitted the entire 45x45 and 2000 word list. When I tried to create a new conditional formatting rule and selected the entire list which is A1:AS45 custom formula and paste =match(A2, indirect("Sheet2!A2:A"), 0) > 0 it didnt work. I also changed the sheet name to the one in the file. I really dont know what Im missing. At this point I will pay for someone to do it if that is allowed. I have attached the sheet that is pretty much how the original is.

[janmorris]

1. it is a grid of words, and a single cell with all the words to check from... that is completely different to "list" of words.
2. you want to check for combinations or partial matches with words.

each of your cells A48:A55 will require different solutions.

regarding
A49: you need to be sure, how do you expect someone to give a solution that will ever meet the requirement when you dont know what it is?
A50: "believed to be", in a similar vein to A49, this is ambiguous
A51: you will have to give a far better explanation of what your intention is, just because you know doesnt mean eveyone else does.
A52: just like A51, you will need to give a far better explanation.
A53: that is an impossibility... google sheets is a number crunching program, not a thesaurus
A54: likely an impossibility because you havent explained the criteria... also, for this to possibly work, your cell in the BIP sheet may require to be split into an actual list
A55: just like the others.. you have not given a very good explanation.

keep in mind that in this section of the forum, people are voluntarily helping you, so to show that you value their time, take some time of your own to write clearly what you are seeking to achieve.

if you are willing to pay for services, then try the commercial services section from the main menu... you will need to get approval to join that section... AND they will still require a better explanation of how you derive your results.

lastly.. i will not attempt to develop any google sheets solution in excel... certain functions are not available due to excel versioning (different versions have different functions.. and none of them have all the functions of google sheets). i provided you with a google sheet that you can easily edit to your requirements.. so instead of using xlsx files, please use the link i gave to provide your samples.

[6StringJazzer]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)