Formula to compare two lists and separate non-recurring items?

T

Tennessee

Does anyone know how to set up a formula to compare two lists of mostly
similar entries and have Excel move or copy any non-repeating entries to a
new column [so that I can see them more easily]?
 
B

bpeltzer

Use the match function to determine if a particular entry is in a different
list. Ex: =isna(match(a2,Sheet2!a:a,false)) will return TRUE if the
contents of a2 do NOT appear in the list in column A of Sheet2. Autofill
that formula through column B, and do the same thing in the list on Sheet2,
referencing Sheet1. The turn on your autofilters and show only the TRUEs;
copy the remaining rows to wherever for further review.
 
T

Tennessee

Thanks for your response!

I will clarify my situation:

The two lists that I'm trying to compare are an audit of e-mail addresses. I
have sorted them alphabetically. The problem is - if an address appears in
column A but not B, then I need to take one action on it. However, if an
address appears on column B but not A, then I need to take a different action
it it. (No action is required if an address appears in both columns.)

Do you know if Excel can do something like that?


bpeltzer said:
Use the match function to determine if a particular entry is in a different
list. Ex: =isna(match(a2,Sheet2!a:a,false)) will return TRUE if the
contents of a2 do NOT appear in the list in column A of Sheet2. Autofill
that formula through column B, and do the same thing in the list on Sheet2,
referencing Sheet1. The turn on your autofilters and show only the TRUEs;
copy the remaining rows to wherever for further review.

Tennessee said:
Does anyone know how to set up a formula to compare two lists of mostly
similar entries and have Excel move or copy any non-repeating entries to a
new column [so that I can see them more easily]?
Click to expand...
Click to expand...
 
Top