Conditional Formatting Assistance

[wendy]

Hello,

I have a spreadsheet that contains multiple columns containing text. I
would like to highlight the unique values that exist in all other columns
when compared to the values of column B.

Any assistance in accomplishing this would be greatly appreciated.

Thanks

 

[Dennis Tucker]

I want to understand this better before I help.

You have lots of columns of text. The text in column B is your reference
for the other columns of text.

So if I;
1. looked at the text in cell C1 and
2. compared it to all of the text in column B and
3. there was NOT a match, then I would
4. highlight cell C1 and
5. move on to cell C2

This process would repeat for all cells that have text but do not belong to
column B.

Is this correct?


Dennis

 

[Rick Rothstein]

Select all the columns from Column C to the last column you want to have
this functionality (doing this should leave C1 as the active cell) and then
try this Condition Formatting formula on them...

=AND(C1<>"",NOT(ISNUMBER(MATCH(C1,$B:$B,0))))

 

[Bob Phillips]

Select all the cells to check, starting with C2, and add CF with a formula
of

=$B2<>C2

then set the highlight formatting.

 

[Rick Rothstein]

Just so you know, your original description was not entirely clear as to
what you were looking for. Bob gave you an answer assuming you were looking
to compare values in a row with the contents of the cell for that row in
Column B whereas I assumed you wanted to check each cell against all the
values in Column B (no matter what row). Probably one of our solutions is
the one you are looking for; but we can't say for sure given the terse
description you provided in your original posting.

Anyway, just to follow up on my solution... I assumed your data started in
Row 1 whereas Bob assumed Row 1 was used for headers and that your data
started in Row 2. If, in fact, your data starts in Row 2, then select all
the cells starting with C2 up to a cell including the last column you want
to check down to a row that is larger than the maximum number of rows you
ever expect to fill data into (I'm assuming that is M1000 for this example)
and use this formula instead of the one I posted earlier...

=AND(C2<>"",NOT(ISNUMBER(MATCH(C2,B:B,0))))