How to extract duplicate records from 4 spreadsheets into a separate spreedsheet?

motivationalistic

New Member
Joined
Nov 23, 2012
Messages
15
Hi Fellows,

I have 3-4 excel spreadsheets with same sort of data. Each spreadsheet has 1000+ records, and each record has data ranging from column A to Z.
Some records are duplicate within these 4 spreadsheets. Now, I want to extract the duplicates into new spreadsheet like;

every record has a unique account number, I want to put a formula against the column which is the account number and it look up that account# in all the spreadsheets and then put the only duplicate records founds into a new spreadsheet from column A-Z (it should only extract the duplicate account numbers found i.e. 2,3 or 4 times duplicate).

Please if someone could help me out to populate that kind of formula.

Thank You!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
This would require some manual labor and wouldn't exactly call a 'clean' solution.

Enter with Ctrl + Alt + Enter
Code:
{=IFERROR(INDEX(Sheet2!A1:A5,MATCH(Sheet1!A1:A4,Sheet2!A1:A5,0)), "")}

In the formula above, you have Sheet2!A1:A5 and Sheet1!A1:A4, which are where your account numbers are stored.
So, any duplicate values would appear in your output column.
However, there will be some blank cells because what the formula is doing, checking each cell in Sheet1!A1:A4 against Sheet2!A1:A5 and making sure nothing is outputted if the account number is unique to its own spreadsheet.

You can copy + paste these as values and filter out the blank cells.

If you want to make this work with 4 spreadsheets, you would need to use the formula 6 times in order to achieve what you want.

I lack knowledge in formula so this is the best I can come up with..

On the other hand, if you use a macro, there wouldn't be any problem in achieving a solution.
 
Upvote 0
Solution
Hi kpark91,

Many thanks for your input.

I was able to locate the duplicate records within 4 workbooks and extracted them separately with the help of "Conditional Formatting" (excel 2010).
I applied conditional formatting multiple times with different criteria and extracted the required data.


Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top