Comparing data in two columns and highlighting the data

[Guest]

I have 2 columns of data that I am trying to compare. 1 column is a long
list cells (d2:d1950) the 2nd column (f2:f95) contains values that may or may
not be in column d.

I've read enough posts to find the formula for if there is a match
(=IF(COUNTIF($D$2:$D$1936,F2)>0,"match","")), but rather really what I am
looking to do is highlight the cell in column D when there is a match and if
there is no match I'd like to hightlight those in column f.

Any help is really really appreciated.

Also I may have several columns f, g, h all the same charecteristics. Would
there be a way to highlight the matches in Column D when they match up with
any cell in f, g, or h and highlight the cell in f, g or h when there is no
match to the range in column D?

Thanks so much
David

 

[Jason Morin]

1. Select D21950 and go to Format > Conditional
Formatting.
2. Select "Formula Is" and put:
=ISNUMBER(MATCH($D2,$F$2:$F$95,0))
3. Press the "Format" radio button and format as desired.
4. OK out.
5. Repeat the process by selecting F2:F95 and using:
=ISNA(MATCH($F2,$D$2:$D$1950,0))

HTH
Jason
Atlanta, GA

 

[Bernie Deitrick]

David,

Select all of column D, and select Format / Conditional Formatting....

Select "Formula is" (instead of "Cell Value is"), and use the formula
=COUNTIF($F:$H,D1)>=1
Set the color formatting as you wish.

Then select columns F (or F, G, and H), and do the same thing, using the
formula (the same for either case):
=AND(F1<>"",COUNTIF($D:$D,F1)=0)

HTH,
Bernie
MS Excel MVP

 

[Guest]

You guys are so good, I hope you get paid good money for all of this help.
These work really well. Can you suggest any good books that go into things
like this? I'm looking for something pretty detailed. Thanks again

 

[Guest]

Sorry one last thing. Is there a way to highlight the entire row where the
match occurs rather than just one cell at a time. I know I could set the
conditional formating to do each column at a time, but is there a function or
macro to do it all at once?

Thanks,
David

 

[Bernie Deitrick]

David,

We get paid the same as you get for posting you question - nada.

Any Excel book by John Walkenbach will be good to learn from. The posts here
are actually the best learning tool available - real-life problems,
real-life solutions.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

David,

Select all the cells, and use the formula
=COUNTIF($F:$H,$D1)>=1

HTH,
Bernie
MS Excel MVP