Thread: Moving Duplicate records to new sheet
View Single Post
 
Old 01-13-2012, 05:16 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
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]
Reply With Quote