Compare two email lists and output non-matching

[Axle_Max]

Hi,

I am a newbie and need your expert help please.

I have two email lists. For example they are:

List 1:

a@spam.com
b@spam.com
c@spam.com

List 2:

abcdef@spam.com
b@spam.com
c@spam.com

In list 1 the non-matching address is a@spam.com. namely it is not common to both lists. I want to automatically go through the lists and output firstly a new list of the non-matches from column 1 and also a new list of the non-common items from list two.

So in this case the result would be that somewhere there is a new list from list 1 that has "a@spam.com" and secondly there is a new list from list 2 that has "abcdef@spam.com."

Obvioulsy the real list is more complicated and longer but the principle will be the same.

Can anyone help please?

[NBVC]

In a column near List1 use a formula like this:

=Isnumber(Match(A1,B:B,0)) where A1 is the first address to search for and B:B is the entire column B that List 2 is located in. Then copy the formula down.

You will get TRUE if matches are found adn FALSE if none are found.

You can then filter this list through DATA|FILTER|AUTOFILTER, filtering for FALSE and copy/paste it elsewhere.

You can repeat the process for comparing List2 to List1 , just changing the references in the formula above to suit.

[Axle_Max]

Hi,

Thank you for the reply. It is much appreciated. Unfortunately this is not the solution that I need. I need a different example to explain. Unfortunately the lists are not aligned for comparison in that way.

List 1

s12345
iuygl
fhflsdihew
couheui
erouieh
etoiujrt
a123
a124

List 2

iuygl
s12345
erouieh
etoiujrt
couheui
a124

If you compared these lists as they are using the solution that you have provided all would read false even though there is significant overlap of content between the lists.

Now I expect that you will say "sort the lists first". In this case your solution is closer to the mark but would still require a lot of manual iterations to come away with the resulting two lists of exceptions that I desire. In theory some lists would require as much work as sorting them amnually.

If you have any ideas, given this new information, I would be very grateful for your expert advice. Thank you again for trying to help me.

[NBVC]

See attached for example of what I meant...

[Axle_Max]

Of course! You are the king. Thank you so much.

[Leith Ross]

Hello Axel_Max,

Here is another version that is fully automated. Click the button and your done. The macro makes use of the Dictionary object to do the comparisons and remove any duplicates. Here is the macro for those who are interested.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Axle_Max]

Where is it please? I can't see a link. Sorry if I am being obtuse.

[obrientech]

Hello all,

i have a question for the excel gurus. i tried using the compare list.xls and when i plugged in my info that i needed compared, i received error 457 "This key is already associated with an element of this collection", then visual basic opened it pointed right to this. can someone help? thank you

Please Login or Register  to view this content.

[obrientech]

sorry for the last post, i went in rolled up my sleeves and figured it out. it was my second table that had incorrect values in it.

Thanks

[jpcrane]

Hello all,

i have a question for the excel gurus. i tried using the compare list.xls and when i plugged in my info that i needed compared, i received error 457 "This key is already associated with an element of this collection", then visual basic opened it pointed right to this. can someone help? thank you

Please Login or Register  to view this content.

I got the same error message. I think it occurred because I had duplicate email addresses. When I took out the duplicates it work great.

Thanks for posting this!

[bugmenot]

You just need to change the tests for List1 and List2 beacuse they are slightly incorrect:

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Do the same for the creation of unique values in List1