Articles

Solved Excel 2007 - Compare 2 columns using conditional formatting

June 7, 2012 at 12:22:19
Specs: Windows 7 Professional

I am trying to compare 2 columns of data in a single worksheet against one another, and am hopingt to use conditional formatting to highlight the cells where certain criteria is met.

For example:

Column D has either blank cells, or the letter A.
Column E has cells with many different values - some numbers, some text, or the letter B.

I want to use conditional formatting to compare Column E to Column D, and to highlight cells in Column E whenever a cell in Column E has the letter B, AND the cell in the same row in Column D has the letter A.

I've tried using conditional formatting, and specifying my own formula, but would have to do this row-by-row. Is there a faster way to do this?

Thanks ~

K


See More: Excel 2007 - Compare 2 columns using conditional formatting

Report •


#1
June 7, 2012 at 12:59:31
✔ Best Answer

Select the entire range you are comparing, such as E5:E10.

Use this as your Conditional Formatting formula:

=AND(D5="A",E5="B")

Excel will take care of updating the formula for each Row.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 7, 2012 at 13:29:59

I just tried that, and either I messed something up, or it's not working correctly.

For the sake of the examples below, please assume the following:
D5 is blank, E5 is B
D7 is A, E7 is B
Other cells in D column are filled randomly with the letter A, or are blank
Other cells in E column are filled randomly with data, or the letter B

When I select the range of cells in column E (ex E5:E10), and apply that conditional formatting using
=AND(D5="A",E5="B")
nothing highlights (E7 should)

I then tried it as an IF function
=IF((AND(D5="A",E5="B")), TRUE, FALSE)
and still nothing highlights (E7 should)

I then tried the formula
=IF((AND(D7="A",E7="B")), TRUE, FALSE)
then EVERYTHING highlights (E5 should not)

I've tested this in 2 different workboks, one of them being a blank new one where I free-keyed the data without copying anything over from the original book.

What am I doing wrong?


Report •

#3
June 7, 2012 at 13:55:26

re: "and either I messed something up"

That's would my guess, since I copied your AND formula directly from your post and it highlighted E7 just fine.

You are actually choosing a format color, aren't you?

Next, when you select E5:E10 and use =IF((AND(D7="A",E7="B")), TRUE, FALSE) then you will get results that look strange since the cells you are comparing won't line up with the cell that gets formatted. e.g. E5 will be have the CF applied if D7 and E7 match the criteria, E7 will be have the CF applied if D9 and E9 match the criteria. Check the CF formula for the individual cells to see what I mean.

By the way, these 2 formulas are essentially the same:

=AND(D5="A",E5="B")
=IF(AND(D5="A",E5="B"), TRUE, FALSE)

By default, an AND function returns TRUE or FALSE, so you don't need the IF if all you want is TRUE or FALSE, which is all that CF needs.

One last check would be to put these formualas in their own cells and see if they return TRUE:

=D7="A"
=E7="B"

If they do, then the problem is definitely with your CF process, not with the data in the cells.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 7, 2012 at 14:24:30

Oh, I truly believe it's something I'm doing, I'm just trying to figure out what.

Yes, I'm selecting a highlight color.

Here are my steps:
*Highlight the range of cells in the column I want with the mouse.
*Click on "conditional Formatting" button in the Ribbon --> choose "New Rule"
*Rule type = "Use a formula to determine which cells to format"
*Set format to highlight in yellow
*Type the following formula in the bar: (this is the real formula I'm using since the Excel version I'm in uses numbers for both rows and columns)
=AND(R1C1="A",R1C2="B")
*Click "Ok"
*Click "Apply"
*Click "Ok"

Nothing highlights.

I tested with the If thinking I messed up the AND, since I like to cover all my bases when searching for the root cause fo a problem.

I've tested the conditional formatting by doing it per row and it works. It's only when I try to do it for the range that it fails.


Report •

#5
June 7, 2012 at 15:11:48

Please post a small sample of your spreadsheet,
but first read this How-To so see how to post your data on this forum:

http://www.computing.net/howtos/sho...

It appears that your doing everything correctly.

Curious why your using the RC notation?

MIKE

http://www.skeptic.com/


Report •

#6
June 7, 2012 at 15:35:34

(this is the real formula I'm using since the Excel version I'm in uses numbers for both rows and columns) =AND(R1C1="A",R1C2="B")

Try this formula:

=AND(RC5="b",RC4="a")

See if that works.

In your formula R1C1 = A1 and R1C2 = B1
your looking form Column 5 which is Column E
and Column 4 which is Column D

gets confusing, that's why I stick with the A1 type notation.

EDIT ADDED:

If are using Excel 2007 and you want to convert to A1 type notation:

Select the Office Button, upper left corner
Select Execl Options, bottom of pop up window
Select Formula in the left panel

Under the Heading: Working With Formulas
UN-check the box next to: R1C1 Reference Style
Click OK


MIKE

http://www.skeptic.com/


Report •

#7
June 7, 2012 at 15:41:46

I'm using the RC notation since my columns aren't labeled A, B, C, D, etc

EDIT:

Thanks for the tip on how to change the columns back to using the alphabet.


Report •

#8
June 7, 2012 at 15:44:31

I'm using the RC notation since my columns aren't labeled A, B, C, D, etc

Don't understand?

MIKE

http://www.skeptic.com/


Report •

#9
June 7, 2012 at 15:54:35

Sorry for the confusion, I - when I was doing the R1C1 formula, that's because I opened a new spreadsheet amd keyed only the 2 columns I was trying to work out the highlighting issues with, just in case it was something in the data itself.

EDIT:

So, I just changed the excel sheet to use the alphabet for my columns, and tried to do this one more time. Found the problem - had to do with absolute values, which I couldn't see when I was using the R1C1 format. Not sure why that was, but at least the problem is solved and I am able to finally get this to work like I know it should.

Thanks to everyone for their assisstance!


Report •

#10
June 7, 2012 at 16:07:08

Found the problem - had to do with absolute values

Sorry I didn't specify that,
but the formula: =AND(RC5="b",RC4="a") (note no row numbers)
is the RC version of: =AND($E1="b",$D1="a")
where the columns are anchored.

MIKE

http://www.skeptic.com/


Report •

#11
June 7, 2012 at 16:22:38

Here's a posting tip:

When you tell people who are trying to help you that you are using a particular formula, tell them the truth.

You said you used:

=AND(D5="A",E5="B") and =IF((AND(D5="A",E5="B")), TRUE, FALSE)

when, in fact, you never did.

What you did do is have me waste time working on a problem that didn't even exist.

Had you told us right up front that you were using R1C1 notation, I could have spent time working on the real issue instead of wasting time testing formulas that you never used and would never have worked.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question