Comparing 2 email lists to find duplicates

Question

Level 1

21 points

Comparing 2 email lists to find duplicates

I am a beginner in Numbers and help would be be much appreciated!

I am trying to identify duplicates in an email list for my club, so that I can clean things up.

I have copied an old email list next to a new email list and saved the document in Numbers. The table looks like this:

First Name	Last Name	email	First Name	Last Name	Email

The first 3 columns are the "newest list" (columns A, B, C). The next 3 columns D, E, F are the "old list'. How do I identify the duplicates?

This may be asking too much, but is there also a way to "highlight" the duplicate cells using cell colour (for example, yellow?).

Thank you.

Stu

MacBook Pro with Retina display, OS X Yosemite (10.10)

Posted on Mar 11, 2015 4:04 PM

Reply

Answer 1

Mar 11, 2015 6:22 PM in response to Stu593

This may help you:

I added columns G thru J.

G2=A2&B2

this is shorthand for... select cell G2, then type (or copy and paste from here) the formula:

=A2&B2

H2=D2&E2

I2=COUNTIF(H,G2)

J2=COUNTIF(G,H2)

column I reports that the name in column A and B is a duplicate of one found in columns D and E

column J reports that the name in column D and E is a duplicate of one found in columns A and B

select cells G2 thru J2, copy

select cells G2 thru the last cell of column J (J7 in my example), paste

you can sort by last name and first if that helps you

Reply

Answer 2

Stu593 Author

Level 1

21 points

Mar 12, 2015 6:25 PM in response to Wayne Contello

This is very helpful. If I understand correctly, if a 1 shows up in either column, then there is a duplicate. If a 0 shows up, for example, in all the columns, then there are no duplicates. Is this correct?

If I wanted to look for duplicate emails (instead of duplicate names) then:

G2=C2

H2=F2

I2=COUNTIF(H,G2)

J2=COUNTIF(G,H2)

Thank you so much for your help! I think that I am starting to understand.

Reply

Answer 3

SGIII

Level 8

37,562 points

Mar 12, 2015 7:53 PM in response to Stu593

is there also a way to "highlight" the duplicate cells using cell colour (for example, yellow?).

In addition to the formula approach Wayne describes, you might also try this Flag Duplicates Automator Service (Dropbox download).

To install double-click the .workflow package and, if necessary, click Install Anyway (or Open Anyway) in System Preferences > Security & Privacy.

Thereafter, to use just select the cells that contain duplicates and choose Flag Duplicates from the Numbers > Services menu.

SG

Reply