Formula for Comparing and highlighting rows

Microsoft Excel 2003 (full product)
August 20, 2010 at 06:22:46
Specs: Windows XP
I have a spreadsheet where I need all of Column B compared to Column C. If the variable in column B does not match anywhere in Column C I need that variable highlited red in Column B



See More: Formula for Comparing and highlighting rows

Report •


#1
August 20, 2010 at 10:23:06
Hi,

Nice to know what you need.

Was there something you wanted to ask for.

Regards


Report •

#2
August 21, 2010 at 05:44:25
I'm sorry I guess I need instructions on how exactly toget excel to perform that function. Is it a formula and if so how is it written?

Report •

#3
August 21, 2010 at 07:24:43
Hi,

Your description of the issue is not entirely clear.

If this is your issue:
There is a series of values in cells in column C (for this example I put data into cells C2 to C14)
and you have a cell in column B containing a value (I used cell B2)
then you want cell B2 to be Red when the value in B2 matches one of the values in cells C2 to C14

Do this:
Select cell B2
From the menu select Format - Conditional Formatting ...
In the 'Condition 1' drop-down select 'Formula Is'
In the formula box enter this:
=IF(ISNA(VLOOKUP(B2,$C$2:$C$14,1,FALSE)),FALSE,TRUE)
Click the 'Format...' button and select a red color in the 'Patterns' Tab
Click OK and OK

Now when the value in B2 matches one of the values in C2 to C14 the cell color will be Red

The conditional formatting formula works like this:
VLOOKUP looks for the value in B2 in the range C2 to C14, as this is a single column range, and we don't need it to return anything else, the column offset is set to 1. FALSE in VLOOKUP ensures that it only returns a value if there is an exact match.
If it does not find an exact match it returns the NA error
The IF function tests VLOOKUP for the error value using ISNA
If VLOOKUP doesn't find a match it returns the NA error and ISNA is therefore TRUE
The structure of the IF function is IF(test, value if True, value if False)
In this case if the test is true i.e. VLOOKUP returned the NA error, IF returns FALSE
If a match was found, VLOOKUP does not return the NA error and the IF ISNA is false and IF returns TRUE

The conditional formatting is applied if the final outcome of the formula is TRUE, in this case when there is a match.

If you copy cell B2 to B3 then B3 will also have the same conditional formatting, but this time B3 is compared to the values in C2 to C14

If your requirements are different, please include all the details - please be specific about cells (compared to Column C means compared to cells C1 to C65536) and be specific about what is being compared to what (all of Column B compared to Column C does this mean B1 compared to C1, all the way down to B65536 compared to C65536)

Regards


Report •

Related Solutions

#4
August 21, 2010 at 11:31:37
ok sorry for the lack of details. I currently have a series of values all in column B from rows 2 to row 800. Now in Column C I have nothing. What I would like to happen is when I scan an item into column C I need it to look thru all of column B and each row of column B to make sure that what I have scanned in matches something anywhere in column B, at the end when I am done scanning all my items into column C I need column B to highlight in red all the values that did not find a match in column C.. So I dont need a 1 to 1 comparison of say b2 to c2 I more so need it to compare both entire columns and make sure there is a match not just direct cell to direct cell. Hope this makes sense

Report •

#5
August 21, 2010 at 11:45:42
Here is another formula to do the same thing, except using the =ISERROR & =MATCH() functions.

=IF(ISERROR(MATCH(B1,$C$1:$C$14,0)),TRUE,FALSE)

MIKE

http://www.skeptic.com/


Report •

#6
August 21, 2010 at 11:54:14
I currently have a series of values all in column B from rows 2 to row 800. Now in Column C I have nothing.

With the above new info in mind, with my formula:

=IF(ISERROR(MATCH(B2,$C$2:$C$800,0)),TRUE,FALSE)

After you enter it into the Conditional Formatting, ALL your data in column B will be highlighted in RED, but, as you enter data into column C that matches any cell in column B the Red will disappear.
So you will be left with the RED cells in Column B only highlighting data that does not appear in Column C.

MIKE

http://www.skeptic.com/


Report •

#7
August 21, 2010 at 13:44:01
Hi,

Use conditional formatting, as described before.

Select Cell C2 (assuming this is the first cell to have scanned data entered into it).

Use this formula in the conditional formatting 'Formula Is' box:
=IF(ISNA(VLOOKUP(C2,$B$2:$B$800,1,FALSE)),FALSE,TRUE)
Note the $ signs in the range B2 to B800, and no $ sign in the C2 address.
Change the format to Green and click OK, OK.

Now select cell C2 and drag it down column C as far as you are likely to have scanned values entered.

As you enter values in column C the cell will be green if the scanned value matches something in column B.

Now in B1 use this formula for the conditional formatting and select a red color:
=IF(ISNA(VLOOKUP(B2,$C$2:$C$500,1,FALSE)),TRUE,FALSE)
Any value in column B that has not been entered in column C will be highlighted in Red.

Change the column C range to match the number of rows you will use for entering your scanned data.

Regards


Report •

#8
August 23, 2010 at 11:45:40
Omg Thank you so much both of the methods described above worked beautifully! Thank you to the both of you so much!

Report •

#9
August 24, 2010 at 03:28:50
You're welcome.

Thanks for letting us know it all worked

Regards

Humar


Report •


Ask Question