matching duplicates in two workbooks

  • Thread starter Thread starter brookelea
  • Start date Start date
B

brookelea

I need to take column A in workbook 1 and find the information that is
not a duplicate of an item on workbook 2.

In otherwords whatever is in workbook 1 AND workbook 2 that matches, i
need to know, so I can filter out what doesnt match.

I have 18,771 items on one report and the other has 13,112. The 13,112
is on the report with 18,771. I need to identify the items that are
not the same as any of the 13112 on the 18771 document.

I hope that makes sense to someone, Its all very confusing to me. I
was shown a vlookup formula that put a certain value from a column
chosen within that document so it would populate in the column next to
items that were on both workbooks, and a #n/a were next to the items
that were not on both workbooks. I tried what I remembered from before
and it didnt work.

Any help would be a great help, this report has to be done by friday!!
 
Workbook 1, sheet 1, column a has 18000+ rows. Workbook 2, sheet1, column a
has 13000+ rows.
Formula in workbook1, sheet1, in cell b1 is
=vlookup(a1,'[workbook2.xls]sheet1'!a:a,1,false) Drag this to bottom of
table. returns value if duplicate, #N/A if not. Filter on column B for #N/A.
These will be unique entries. Have not tested, but this should work.

HTH

Bruce Girvitz
 
I am not sure I am clear about what you want, but...
Enter this formula in an empty cell on Sheet1 and copy it down on that
worksheet to the end of your data. TRUE will be returned if there is a
matching value on each worksheet.

=COUNTIF(Sheet2!A1:A10,A1)>0
 
Bruce,

This is the exact formula I have been trying, however it isnt working.
it returns #n/a for all 18000, which is not correct. any ideas of what
I might be missing?

Brooke
 
Make sure both workbooks are in the same directory, then remove the
apostrophe's. Here is some syntax from a quick test that works for me.
=VLOOKUP(A1,[Gord.xls]test!A:A,1,FALSE) - substitute with your ranges etc. U
need to name the workbook as well as the appropriate sheet.Also, make sure
the brackets are the square brackets, not the curly ones. Let me know if u
have additional problems.

Bruce Girvitz
 
Back
Top