Checking of cells with other cells (text)

Not seeing data layout (and especially synonyms spreadsheet) is not so easy to suggest particular method. Could be that simple index-match solution and counting unique values would do.

Moreover you mentioned conditional formatting - it will be visible as soon as first words are entered
how about misspeled words like swaeter, junper ;-) etc.

Thanks for you reply!!

I'm open to modifying the layout of the synonym spreadsheet, as I would like it to support whichever system is used for the auto-check.

For now, what we have is a very simple spreadsheet that has a list of words, and synonyms side by side. Something like this: (can't seem to attach a sample file here :/)

Accepted words <------> Synoynms
Shirt <------> Long Sleeved Shirt, Short Sleeved Shirt
Trousers <------> Pants
Pants <------> Trousers
T Shirt
Long sleeved Shirt <------> Shirt
Short sleeved Shirt <------> Shirt
Bermudas
Dress


No need to worry about misspelling as well, we are able to check for spelling as we record the words down.

I'd go for VBA, but if you want to avoid this, and as you said in post #1 flag with conditional formatting, consider conditional formatting with a rule based on a formula.

example formula:

Formula:

Please Login or Register  to view this content.

it calls for different arrangement of synonyms/acceptable words table - see second sheet:

HTML Code: 

note that the formatting is applied at the moment in column A (rows 2:21) other columns are just description to the sample and could be deleted

Hi,
the formula works opposite way, it highlights unique values (duplicates stay untouched). To highlight duplicates it should be negated.

Formula:

Please Login or Register  to view this content.

As you noted first vlookup just checks if a word is anywhere on a list (could use here MATCH as well, but the same formula is used also in second part). So it it is listed - returns a synonyms group number if not returns error resulting in no CF appled in that cell

As for:

Formula:

Please Login or Register  to view this content.

the innermost part is:

Formula:

Please Login or Register  to view this content.

as we copy it down it becomes

Formula:

Please Login or Register  to view this content.

and so on.
so the output is a table 8 rows (1:8) by n columns
we do transpose to change A1:An rows into columns
the table contains comparisons of A1...An values with allowed item names.
Then we have IF ariound it:

Formula:

Please Login or Register  to view this content.

is comparison is true the position in table is substituted with "number of synonyms group" else it is substituted with empty string
then we compare number of group for text entered (A2) with every item in a table

Formula:

Please Login or Register  to view this content.

and result is table of boolean values true/false
so we convert these values to numbers using just

Formula:

Please Login or Register  to view this content.

and sum all these numbers with simple

Formula:

Please Login or Register  to view this content.

If there was any item from the same synonyms group in cells above, the sum will be non-zero, wile if it is first occurence of given group the sum is 0.

Now we do negation (so the number is now treatred as boolean value - if 0 then treated as false , any other number treated as true) and negated true is false (of course negated false is true)
We multiply this (so again true becomes 1 and false 0) by te Vlookup from the front and here we are

... writing this (still probably not very descriptive text) took me (i think) some 20 times longer than writing the formula :-D

Note that in transpose(A$1:An) we start from the cell not containing any word expected. we could start from A2 but then we shall have separate conditional formatting for cell A2 and other for all next cells, so I did it that way to have just one universal formula.

Hope it clears view a bit

I do not think it possible with this approach. Being synonym is non-herarhical.

If A is synonym of B then automatically B is synonym of A.

This what you described is rather taxonomy
cat is a mammal
dog is a mammal
but dog is not cat

Basically - yes (not with such ease as synonyms), but it is already pretty far from the place where we started:

I conduct tests for people, and one of these test requires people to list as many words as they can in a certain category, say "Clothing". As there are often many synonyms (e.g. Trousers & Pants; Jumper & Sweater), we have a spreadsheet where we list what are acceptable words, and their synonyms. However, it takes much time to manually go through this spreadsheet to check for synonyms, for a list of 20-odd items.

So if your opening question is answered, I'd suggest marking thread solved and possibly start new one, keeping in mind how usefull the attachment could be