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" <DavidKinsley@discussions.microsoft.com> wrote in message
news:0188FA83-09B4-4238-8FA1-20E3987FD020@microsoft.com...
> 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" <DavidKinsley@discussions.microsoft.com> wrote in message
> news:0188FA83-09B4-4238-8FA1-20E3987FD020@microsoft.com...
> > 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" <DavidKinsley@discussions.microsoft.com> wrote in message
> news:0188FA83-09B4-4238-8FA1-20E3987FD020@microsoft.com...
> > 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" <DavidKinsley@discussions.microsoft.com> wrote in message
news:35E1D9EC-A3A8-48F2-BE08-276448340810@microsoft.com...
> 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" <DavidKinsley@discussions.microsoft.com> wrote in message
news:83B95E5F-596E-4642-8273-E69DBE20AEDA@microsoft.com...
> 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" <DavidKinsley@discussions.microsoft.com> wrote in
message
> > news:0188FA83-09B4-4238-8FA1-20E3987FD020@microsoft.com...
> > > 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
> >
> >
> >