comparing two lists of last names

[rbentzlin]

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.

 

[Reitanos]

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.

 

[Max]

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

 

[rbentzlin]

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

 

[rbentzlin]

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]

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.

---

 

[monsta]

How about using a helper column and the following formula:

{=OR(EXACT(B1,A$1:A$266))}

This will return either TRUE or FALSE. Conditionally format the names that return FALSE (eg - =C1=FALSE).

 

[Max]

well ?

---