Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find duplicates between multiple XLS files
I have two separate Excel spreadsheets. One has about 15,000 records and the other has about 350 records. I want to look for duplicates between the two. What are my options? I have attached the two files. Both files have a "title" field and both have an "issn" field. ISSN = International Standard Serial Number. I think comparing either of these would work. I can do this by hand (print out the sheets and look at them), but I wanted to see whether Excel could do the work for me. Thank you for your time, Culley -- Culley ------------------------------------------------------------------------ Culley's Profile: http://www.excelforum.com/member.php...o&userid=29309 View this thread: http://www.excelforum.com/showthread...hreadid=490286 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find duplicates between multiple XLS files
1. In each spreadsheet insert two new columns next to the ISSN column.
Label one column "Appears in this sprdsht" and the other column as "Appears in other sprdsht". Assume, for the moment, your ISSN is in column A for each spreadsheet. 2. For each row, enter this formula in the "Appears in this sprdsht" column: =COUNTIF($A$1:$A$15000,A1) Copy this cell, and paste into each row. This counts how many times that ISSN appears in THIS spreadsheet. The results should be one, meaning no duplicates, for all entries. 3a. For each row in the 350 record sheet, enter this formula in the "Appears in other sprdsht" column: =COUNTIF(Big!$A$1:$A$15000,A1) Copy this cell, and paste into each row. The result here should be zero, meaning no duplicates. 3b. For each row in the 15,000 record sheet, enter this formula in the "Appears in other sprdsht" column: =COUNTIF(Little!$A$1:$A$350,A1) Copy this cell, and paste into each row. The result here should be zero, meaning no duplicates. You can sum each of these columns. The sum of the "Appears in this spreadsheet" column should equal the number of rows in that spreadsheet; the sum of "Appears in other spreadsheet" should be zero. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find multiple cells/replace whole cells w/data | Excel Discussion (Misc queries) | |||
Inserting Sheets from multiple files | Excel Discussion (Misc queries) | |||
Exporting multiple sheets to multiple htm files? | Excel Discussion (Misc queries) | |||
How can I have excel search and add multiple cells to find a targe | Excel Discussion (Misc queries) | |||
How to find the minimum value in a database with multiple values . | Excel Discussion (Misc queries) |