Compare and extract duplicates

**grouchmax** · 05-05-2009, 11:07 AM

Hi
I am comparing sheets of informaton and looking for email address duplication. Comparing sheet 1 to sheet 2 using MATCH and Using "NEW" or "DUPLICATED" as the results.What I would like to use is sheet 3 and extract the "DUPLICATED" data together with all the information in the same line of the duplicate data. In effect changing "DUPLICATED" to a line of information including the duplicate data.
Any help will be gratefully appreciated.
Cheers

**NBVC** · 05-05-2009, 11:11 AM

Filter for Duplicated through Data|Filter|AutoFilter and copy/paste to Sheet3

**grouchmax** · 05-05-2009, 11:16 AM

Thanks for that. That is what I am having to do now. I was trying to automate the method as I have some 10,000 lines
Cheers

**NBVC** · 05-05-2009, 11:19 AM

Can you post a sample size version of your sheet, showing what you generally have now.

**grouchmax** · 05-05-2009, 12:01 PM

I have scalled down the process here. But in effect on sheet3 were "Duplicate" as shown in A3 I would like this to be replaced with information contained on line 5A to 5I of sheet 2 and in the same columns. If "NEW" then I do not require that information to be captured. Sheet 3 would then become the duplicated information file only. I hope that isnt too confusing. Many thanks for your time I appreciate it.
Rgds
Grouchmax

**NBVC** · 05-05-2009, 12:26 PM

See attached.

In Sheet2, formula in J2 to find duplicates is:

Please Login or Register  to view this content.

copied down

in Sheet3, formula in D1 to find count of duplicates in Sheet2:

Please Login or Register  to view this content.

Formula in E3 of Sheet3 to extract Duplicate Emails:

Please Login or Register  to view this content.

This formula must be confirmed with CTRL+SHIFT+ENTER keys to produce the { } brackets around the formula... then copied down as far as you want.

Formula in A3 of Sheet3 to get other relevant data from duplicate rows...

Please Login or Register  to view this content.

copied across to D3 and down as far as you want.

Then copy formula from D3 to F3 and then copy that to I3 and down as far as you want.

Note: Change all ranges in all formulas in Sheet3 to suit your actual data.

You will need to reconfirm formula in E3 with CSE keys if you change ranges...then copy it down again.

**grouchmax** · 05-05-2009, 12:45 PM

That is terrific thanks very much for your speedy help. Your a star. :-)

**NBVC** · 05-05-2009, 12:56 PM

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save