I have two lists of employee names. One list (List A) has 266 names, the
other (List B) has only 106 names. I am trying to create a formula that
would highlight the individuals that are in List B (106 names) that are not
in List A (266 names). Any help you can provide would be greatly appreciated
as I have spent a fair amount of time trying to figure this dandy out!

Thanks.

Actually highlighting is tricky, but if you have a helper column and
add this formula in column C (for example):
=IF(ISERROR(MATCH(A1,$B$1:$B$106,0)),"","*")
you could then use Conditional Formatting to highlight based on the
asterisk.

For the Conditional Format use a formula:
=C1="*"
and select a fill color. Use the Format Painter to copy the format
down the length of your 266 names and you're done.

On May 14, 6:34 pm, rbentzlin
wrote:
I have two lists of employee names. One list (List A) has 266 names, the
other (List B) has only 106 names. I am trying to create a formula that
would highlight the individuals that are in List B (106 names) that are not
in List A (266 names). Any help you can provide would be greatly appreciated
as I have spent a fair amount of time trying to figure this dandy out!

Thanks.

Assuming List A / B are in cols A & B
Select col B (B1 active)
Click Format Conditional Formatting
Under Condition 1, set it as "Formula Is":
=AND(ISERROR(MATCH(B1,$A:$A,0)),B1<"")
Format to taste OK out

The above will highlight the individuals that are in List B
that are not in List A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"rbentzlin" wrote:
I have two lists of employee names. One list (List A) has 266 names, the
other (List B) has only 106 names. I am trying to create a formula that
would highlight the individuals that are in List B (106 names) that are not
in List A (266 names). Any help you can provide would be greatly appreciated
as I have spent a fair amount of time trying to figure this dandy out!

Thanks.

no dice on either of these formulas listed. The first one doesn't yield any
results and the 2nd one (conditional formating suggestion below) highlights
everything according to my formatting choice (i.e. yellow).

I'm using excel 2007, so I go to the Home toolbar Conditional Formating
New Rule "Use a Formula to determine which cells to format" enter formula
provided below format to highlight cells ok out.

It highlights everything as I said above. Am I doing something wrong?

Thanks for your help,
Ryan

"Max" wrote:

Assuming List A / B are in cols A & B
Select col B (B1 active)
Click Format Conditional Formatting
Under Condition 1, set it as "Formula Is":
=AND(ISERROR(MATCH(B1,$A:$A,0)),B1<"")
Format to taste OK out

The above will highlight the individuals that are in List B
that are not in List A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"rbentzlin" wrote:
I have two lists of employee names. One list (List A) has 266 names, the
other (List B) has only 106 names. I am trying to create a formula that
would highlight the individuals that are in List B (106 names) that are not
in List A (266 names). Any help you can provide would be greatly appreciated
as I have spent a fair amount of time trying to figure this dandy out!

Thanks.

no dice on either of these formulas listed. The first one doesn't yield any
results and the 2nd one (conditional formating suggestion below) highlights
everything according to my formatting choice (i.e. yellow).

I'm using excel 2007, so I go to the Home toolbar Conditional Formating
New Rule "Use a Formula to determine which cells to format" enter formula
provided below format to highlight cells ok out.

It highlights everything as I said above. Am I doing something wrong?

Thanks for your help,
Ryan


"Reitanos" wrote:

Actually highlighting is tricky, but if you have a helper column and
add this formula in column C (for example):
=IF(ISERROR(MATCH(A1,$B$1:$B$106,0)),"","*")
you could then use Conditional Formatting to highlight based on the
asterisk.

For the Conditional Format use a formula:
=C1="*"
and select a fill color. Use the Format Painter to copy the format
down the length of your 266 names and you're done.

On May 14, 6:34 pm, rbentzlin
wrote:
I have two lists of employee names. One list (List A) has 266 names, the
other (List B) has only 106 names. I am trying to create a formula that
would highlight the individuals that are in List B (106 names) that are not
in List A (266 names). Any help you can provide would be greatly appreciated
as I have spent a fair amount of time trying to figure this dandy out!

Thanks.

I don't have/know xl07 so I can't really say anything about your steps taken
over there, albeit it does sound equivalent. But I presume you did ensure
that you selected the col B before you proceeded with the rest of the steps,
re the step mentioned:
Select col B (B1 active) ...

Anyway, let me offer you a nice sample (xl03) with dummy data which demo's
the earlier in working order (it should work in xl07 as well):
http://www.freefilehosting.net/download/3h8fl
CF to show List B not in List A.xls

If your actuals doesn't work as well (some match/some don't, altho' they
"should"), that means that your data is not consistent, eg there could be
extra "white" leading/in-between/trailing spaces within the names which are
pretty difficult to detect visually. You could try using TRIM to clean up
both Lists A / B to improve robustness of matching. Eg place in C1:
=TRIM(A1), copy to D1, fill down all the way. Then copy cols C & D, overwrite
cols A & B with a paste special as values.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"rbentzlin" wrote:
no dice on either of these formulas listed. The first one doesn't yield any
results and the 2nd one (conditional formating suggestion below) highlights
everything according to my formatting choice (i.e. yellow).

I'm using excel 2007, so I go to the Home toolbar Conditional Formating
New Rule "Use a Formula to determine which cells to format" enter formula
provided below format to highlight cells ok out.

It highlights everything as I said above. Am I doing something wrong?

Thanks for your help,
Ryan

well ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---