Moving Duplicate records to new sheet

[Woolstar]

Hi, i have a list of over 100,000 records - first name / last / position / company / email / phone number.



What i am looking for is a macro that will allow me to move all of the duplicate rows (based on the email address) to another work sheet. Leaving the unique. Or the other way around - moving the unique to be left with just the duplicates.

Been searching forums, have not managed to find a solution to my problem.
I hope you can help.

regards

[macropod]

Hi Woolstar,

Are the different parts of each record in separate columns and, if so, which column contains the email address? Are the records sorted by email address? Does it matter whether the macro changes the sort order?

[Woolstar]

Hi,

Thanks for your reply, sorry mine is delayed.

The column headings are as follows -
A - first Name
B - Last Name
C - job Title
D - Company
E - Email addresses

Currently they are not ordered in any way however it does not matter if the macro needs to order them.

[macropod]

Hi Woolstar,

You can probably do it without a macro. Assuming your column has aheader row:
• In F2 insert =A2&B2&C2&D2&E2
• In G2 insert =COUNTIF(F$1:F2,F2)
• Copy both formulae down to the last row.
• Copy column G and paste it as values (via Paste Special)
• Sort the worksheet data by Column G. All unique and 'first occurence' records will now be grouped together with a value of 1 in column G, whilst all duplicates etc will have a value of 2 (or more).
• Simply cut & paste the duplicates etc to the other worksheet.

[Woolstar]

Thanks for your post.

I do believe that this will separate the unique records and first occurrences from the duplicate records (If the email address occurs again)

Is it possible to remove all counts of a duplicate including the first occurrence of it. To be left with just the rows where the email addresses that have only occurred once?

Again, thanks for all your help

[macropod]

Hi Woolstar,

It's not clear what you want.

To exclude email addresses from the test, change the first formula to:
=A2&B2&C2&E2
To inc;ude the 'initial' occurences of duplicates amongst the duplicates, , change the second formula to:
=COUNTIF(F:F,F2)