View Full Version : Solved: non standard list comparing

05-04-2011, 08:19 AM

I am trying to compare 3 lists. If a word is found in all the 3 of them, then the cell that contains that word is formatted with a yellow background on the 1st list.

The problem is that 1st list contains only 1 word per cell, but the 2nd and 3rd list contain more than one!


A ------------ B ------------C

hello -----hello there ------- hey hello

in this case cell A1 changes color since the word HELLO is contained in all the 3 cells!

note: the rows are NOT aligned, which means the formula or function should look in the entire columns and not only in the corresponding row

I thought about conditional formatting or vlookup, but after having spent a couple of hours trying to get it working... it didn't!

Any ideas?

05-04-2011, 08:34 AM
You might try CF on A1 with the condition
=(2 = SIGN(COUNTIF(B:B, "*"&A1&"*")) + SIGN(COUNTIF(C:C, "*"&A1&"*")))

05-04-2011, 09:03 AM
you are the man!!! It works ;)

Thank you so much! :thumb

05-04-2011, 04:19 PM
=(2 = SIGN(COUNTIF(B:B, "*"&A1&"*")) + SIGN(COUNTIF(C:C, "*"&A1&"*")))
Does the 2 indicate to look in 2 other columns? (Sorry Mike, I wasn't thinking clearly before sending)

Having applied myself a bit more to this issue. "Sign"determines the sign of the number generated by the "countif". So, if it doesn't find the value it is seeking, it returns a Zero, and it returns a One if it does.

Providing the CF finds the value in both columns, the formula would be
=(2=1+1) which returns true. But what if it only found the value in one column?

05-05-2011, 01:06 AM
Then the formula would return FALSE, which matches the condition "found in all the 3 of them".

05-05-2011, 01:39 AM
Sorry but you have lost me here.

05-05-2011, 03:37 AM
The desired condition was that the values from A, B and C would all match (up to A being a sub string of B and C)
The formula will return 0 of A is a sub string of nothing in either B or C, 1 if A is a sub-string of an entry in only one column, and 2 if A matches something in both B and C.

05-05-2011, 02:20 PM
Then the formula would return FALSE, which matches the condition "found in all the 3 of them".

Yes I follow that bit, so should this statement have read

"Then the formula would return FALSE, because it did not match the condition "found in all the 3 of them"."

05-10-2011, 12:59 AM
new problem, don't know if it's appropriate to continue the discussion here, as the post has been tagged "solved" already... anyway:

now I need to use conditional formatting to cover a special case:

3 rows again,

how would I need to modify this formula (or should I need a completely new one?)

=(2 = SIGN(COUNTIF(B:B, "*"&A1&"*")) + SIGN(COUNTIF(C:C, "*"&A1&"*")))

to find this condition:

if a SPECIFIC word (which will be always the same fixed 3 letter code, let's call it ZZZ for now) plus the content of cell A1 (also this is a 3 letter code but is not fixed, because every cell will have a different content) is found in column C or B, then highlight the cell in C or B that contains this match.

note: columns C & B contain multiple words, for example cell C1 contains:

"cat dog zzz mountain"

so if cell A1 contains the word cat, then this formula would be satisfied because C1 contains both cat(a1 content) + zzz (fixed search parameter)

I think this is complicated, because countif doesn't allow a double criteria in the expression to define the search parameter..

maybe combine 4 countif expressions, 2 for each column that need to be satisfied to return TRUE?

05-10-2011, 01:57 PM
solved guys, thanks anyway!