Comparing data in two columns and highlighting the data
 

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

1. Select D2:D1950 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

-----Original Message-----
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
.

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

"David Kinsley" wrote in message
...
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

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

"Bernie Deitrick" wrote:

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

"David Kinsley" wrote in message
...
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

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" wrote:

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

"David Kinsley" wrote in message
...
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

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

"David Kinsley" wrote in message
...
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

David,

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

HTH,
Bernie
MS Excel MVP

"David Kinsley" wrote in message
...
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" wrote:

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

"David Kinsley" wrote in
message
...
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