Formula or VBA to remove BOTH records in column, not just one

[iantix]

Hi there,

I'm using a solution in this thread http://www.excelforum.com/showthread.php?t=767947 as I have a lot of email addresses (700k), and I can remove duplictae funtcion BUT I want to remove the duplicate AND the original, to leave just the email addresses that occur once.

I used the solution in the linked thread above;

No, I would create a new column to filter on. Let's say your addresses are in A2:A5000, in E2 dragged down
=COUNTIF($A$2:$A$5000,A2), then Filter on all values greater than 1 and delete those rows. Will that work for you?

BUT on this many rows, it slows EXCEL right down.

Is there;

- a formula to flag those unique values OR
- a VBA to extract them from the range

or another suggestion.

Thanks,

Ian

[JohnTopley]

Sort the e-mail addresses (say column A)

Leave A1 blank

in B2

=IF(A2<>A1,1,0)

and drag down

Filter on 1 and copy/paste column A to new sheet or new column

[iantix]

Thanks - that highlights the doubles where 2 the same exist - and of course would leave ALL unique values in the column, as would the 'remove duplicates' function.

What I want to do is remove BOTH where a pair of email address the same exist.

That would leave, those that are NOT in both of my source lists which I have combined.

[JohnTopley]

Post a file as it is not clear what you are comparing.

[iantix]

Okay - anonymous example attached.

I have two lists of emails. One in column A, one in column D.

I have put BOTH into column G and sorted by name A-Z.

I know that there are 10 emails in List1 that are NOT in LIst2.

I need a formula in column H, applying to column G which will;

- see both parts of a duplicate pair, and either REMOVE both - leaving the 10 unique singles
OR
- flag in column H where a unique single occurs, meaning I can sort column H and just see the 10 unique emails that are in list 1 and not in list 2

Needs to run on 700k email addresses.

Hoping that's clearer, and thanks,

Ian

[iantix]

I've figured it out - the logic baffled me initially.

I added the formula =IF(AND(G2<>G1, G2<>G3),"UNIQUE","PAIR") to cell H2.

Attached is the resulting sheet, with a revised COUNT formula in I2.

Ian