Find and identify duplicate data across multiple workbooks

Book 1 sheet1 A

1234567
1234567
2658798
4083029
5507260
6931491
8355722
9779953
11204184
12628415
14052646
15476877
16901108

Book 2 sheet1 A

2658798
4083029
5507260
6931491
8355722
26587965
26365785
26143605
25921425
25699245
25477065
25254885
25032705
24810525

I need get the result Book 3 Sheet 1 A
All duplicates items from book 1 and book 2


Any help will be appreciate.. thanks!!

Are you really using XL2003 as your profile states? If not, please update your profile.

Will all three workbooks be open at the same time?

Pete

no I am using XL 2010. I have updated my profile.
Yes open 3 work books same time.

And how would you like the duplicates reported? You could have numbers duplicated within Book 1 (but not appear in Book 2), you could have the opposite, i.e. numbers duplicated in Book 2 but absent from Book 1, and you could have a number in Book 1 which is duplicated in Book 2. Would it be better to have these situations shown in separate columns of Book 3 ?

Pete

I actually need to do is find out if there are any duplicates between these two files that are listed.

Is there a way to compare one file against the other file and find out if duplicates exist and how many?

Assuming that you have a header row in Book1 so that your data starts in A2, and that all the files are open in the same instance of Excel, put this formula in B2 of Book1:

=IF(ISNUMBER(MATCH(A2,[Book2.xlsx]Sheet1!$A:$A,0)),A2,"")

and put this formula in C2:

=IF(B2="","",IF(COUNTIF(B$2:B2,B2)=1,MAX(C$1:C1)+1,""))

Copy both down to the bottom of your data. Then in Book3 you can have this formula:

=IFERROR(INDEX([Book1.xlsx]Sheet1!$B:$B,MATCH(ROWS($1:1),[Book1.xlsx]Sheet1!$C:$C,0)),"")

then copy this down until you start to get blanks.

Hope this helps.

Pete