Finding duplicate data between 2 spreadsheet workbooks?

J

jonathan182

Hi I have 2 spreadsheet workbooks in excel, and I want to see if there is any
data that is in both of the spreadsheets. Is there a way of linking the two
to be able to find the duplicate data or any other way for that matter?

Please help!
 
R

Roger Whitehead

Hi Jonathan,

Can you clarify? You have two *workbooks* (Excel files), or two *worksheets*
(ie, in the same file).
A little info about the kind of data they hold may help - is there a column
in each that is designed for unique values? Are the columns in the same
order?

Thx
Roger
 
J

jonathan182

Hi Roger, I have two seperate workbooks. They are both workbooks containing
customer's information. Each customer has a unique, customer reference
number. So there is a column in each workbook with *Customer Reference
Number* as the heading and I wanted to know if there was a quick and
hopefully easy way of finding any customer reference numbers that appear in
both workbooks. The only thing that may be a problem is that the workbooks
havent got all of the same number of columns with the same data, but they
both have the *Customer reference number* column which I need to see if there
are any customer reference numbers that appear in both workbooks. The
customer reference numbers are 1 letter and 7 numbers eg. A0003264.

I hope this is enough information and thanks for taking the time to help me!!
 
R

Roger Whitehead

That's good. Use the VLookup function to lookup the Cust Ref No from Book1
in Sheet2 (or the other way round).
eg: =Vlookup(A1,[Book2]Sheet1!A1:!65536,1,False)
If the #N/A Error is returned, the Cust Ref No does not exist in Book2. If
the Cust Ref No is returned, it exists in Book2. You can select all of the
errors, by use of
Goto (the F5 key), Special, Formulas, uncheck everything but 'Errors', or
simply use Autofilter.

HTH, post again if not...
 
J

jonathan182

Thanks Roger, this has helped me a great deal!

Roger Whitehead said:
That's good. Use the VLookup function to lookup the Cust Ref No from Book1
in Sheet2 (or the other way round).
eg: =Vlookup(A1,[Book2]Sheet1!A1:!65536,1,False)
If the #N/A Error is returned, the Cust Ref No does not exist in Book2. If
the Cust Ref No is returned, it exists in Book2. You can select all of the
errors, by use of
Goto (the F5 key), Special, Formulas, uncheck everything but 'Errors', or
simply use Autofilter.

HTH, post again if not...

--
Roger





jonathan182 said:
Hi Roger, I have two seperate workbooks. They are both workbooks
containing
customer's information. Each customer has a unique, customer reference
number. So there is a column in each workbook with *Customer Reference
Number* as the heading and I wanted to know if there was a quick and
hopefully easy way of finding any customer reference numbers that appear
in
both workbooks. The only thing that may be a problem is that the
workbooks
havent got all of the same number of columns with the same data, but they
both have the *Customer reference number* column which I need to see if
there
are any customer reference numbers that appear in both workbooks. The
customer reference numbers are 1 letter and 7 numbers eg. A0003264.

I hope this is enough information and thanks for taking the time to help
me!!
Click to expand...
Click to expand...
 
Top