#1
|
|||
|
|||
Moving Duplicate records to new sheet
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 |
#2
|
||||
|
||||
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?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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. |
#4
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
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 |
#6
|
||||
|
||||
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)
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Construct a summary sheet by summing up from one or more than one sheet. | PRADEEPB270 | Excel | 1 | 11-04-2011 03:46 AM |
Merge Doc skips records | eonelson | Excel | 11 | 01-30-2011 03:49 PM |
Moving formula range multiple cells when moving sum over one cell | FraserKitchell | Excel | 4 | 02-26-2010 10:38 AM |
copy cell from sheet 2 to sheet 3 macro | slipperyjim | Excel Programming | 1 | 02-18-2010 01:31 AM |
Help with consolidating multiple records into one | wbiggs2 | Excel | 0 | 11-30-2006 01:02 PM |