Comparing Two Columns of Emails / EXACT doesn't work

[SpeakingPeace]

I have two columns of emails of about 7,000 rows each. One column has 7 more emails than the other column. I'm trying to find the values of these 7 emails.

The problem is that Excel doesn't sort these two columns the same way even when I'm doing it exactly the same in the same spreadsheet with these two columns right next to each other. So, I can't use --- =EXACT(M2, P2) --- function to figure this out easily. As an example, in one column Excel sorts an email that starts like "al-" at the beginning of the al's while the other column sorts it at the end of the al's. Given this one example, there might be 15 to 25 emails in which the EXACT function produces a FALSE value. This happens for any email that has a "-" it so given I have 7,000 records to contend I'm not sure how to easily identify these 7 unique emails.

Anyone have an idea?

[AZ-XL]

Upload the sample file. Only then we can say it is possible or not

[popipipo]

Please Login or Register  to view this content.

Try this one

[David_Schop]

How about using vlookup? Do a search from comparing the larger column to the smaller and you "should" end up with 7 values (or more) as #N/A . Those values mean that they are not in that smaller list. You could also do this the other way and verify everything on the larger list is also on the smaller one. If the larger list starts at A1 and the smaller list starts at B1 then in C1 you can use the following formula.

Please Login or Register  to view this content.

If the email address exists in both lists, then the result will be a blank cell. If the email does not exist it will identify it with a "New Email" flag.

Then copy that formula down the C column and you will see the results. This works better than exact because using VLOOKUP doesn't require sorted information.

[Debraj Roy]

Ohh excel 2013..

Try this..

Select Column M & P...
Use Conditional Formatting > Highlight Cells Rules > Duplicate Values..
In the popup box.. Select Unique, instead of Duplicate..

It will highlight all Unique data, in your case only 7 cells.. in bigger Column..

[SpeakingPeace]

Thanks David ! This did the trick.

[David_Schop]

Glad I could help.

[SpeakingPeace]

Brilliant! That way works too. Thanks