Identifying duplicate data between workbooks

**scaffdog845** · 04-22-2009, 06:45 PM

All,

Hopefully I am overlooking a simple solution. I frequently have reports to make based on Work Order numbers. I am looking for the easiest way to find duplicate Work Order numbers that exist in 2 separate Workbooks.

EX. Workbook 1 Sheet one contains the numbers 1-100 in A1:A100
Workbook 2 Sheet one contains X amount of the numbers between 1-100 located somewhere in A:A. For arguments sake let's assume those numbers are 3,6,33,87,99.

What would the formula be to return the values that are in both of the workbooks?

As always any help is greatly appreciated.

Steve

**JBeaucaire** · 04-22-2009, 07:15 PM

Hehe, the answer is 3,6,33,87,99.

You really need to imagine this a couple steps further.

Sheet1 and Sheet2 both represent the same kind of data? Active work orders...complete work orders? Sometimes they get listed in one book and sometime in the other? Or is one of them some sort of master record?

Next, what do you mean when you say "return the values"...return it where? A third workbook? Some column in Sheet2? In Sheet1? In both? Do you want this to happen when you run a macro (on-demand) or in realtime as work orders are entered?

Don't answer part of these questions, think about and answer them all.

**scaffdog845** · 04-24-2009, 07:51 AM

Good Points. I run a report in SAP 2 times a week which gives me all the WO's in our system with open steps in the past. I import this information into an Excel workbook, identify the steps that need to be fixed then email it to the responsible parties. At the end of the week, I run the same report then import it into a new Excel workbook. At this point I have to manually check all of the numbers between the separate workbooks to see which numbers are still in the second workbook. This tells me which WO's have not been fixed. The formula, or macro, etc should be in the second workbook. There are general 3-5 hundred results that I have to manually check for duplicates. EX.

Workbook 1 sheet 1 contains the following in column A
1911717
2107311
2231508
2346278
1886913
etc.

Workbook 2 sheet 1 contains the following in column A
1678926
2231506
2871237
1911717
2553334
etc.

In workbook 2 sheet 1 column B, I would like it to return the value "Duplicate" in the same row as any value which exists in both workbooks. In the example above the end result in the fourth row of book 2 would be
A B
1911717 Duplicate

Thanks

**JBeaucaire** · 04-24-2009, 11:18 AM

Try this in B2 of the second report and copy down...adjust the book names as needed in the first formula, get an answer, then drag/copy it down.

=IF(ISNUMBER(MATCH(A2,[Book1]Sheet1!$A:$A,0)),"Duplicate","New")

**scaffdog845** · 04-24-2009, 11:57 AM

When in doubt the Gurus help out. Worked perfectly... I wouldn't have thought of using the ISNUMBER and MATCH nestings.

Thanks!

**JBeaucaire** · 04-24-2009, 11:58 AM

No worries, it's always easy when you've had to do it before...now that tricks in your own arsenal, eh?

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]