View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave O
 
Posts: n/a
Default 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.