creating a reconciling list of items not matched between two files

[Guest]

I am trying to reconcile 2 xl files and need to generate two lists of items
not included on the other worksheet. ie. On file 1 not on file 2, and, on
file 2 not on file 1.
Both files have two colums. Column A is a 6 digit number and column B is a
dollar value. Both files have in excess of 10,000 rows. List of reconciling
items needs to show data from both columns.
Both files can be merged to the same worksheet if this simplifies the
solution.
I have used VLOOKUP and filtered by #N/A and then cut and pasted visible
cells to create my list but was hoping for a simpler solution.

 

[Guest]

I have used VLOOKUP and filtered by #N/A and then cut and pasted visible

cells to create my list but was hoping for a simpler solution.

Above method looks fine to me.

Not sure whether using MATCH would be significantly faster,
but you could try it out like this ..

Assuming both Book1.xls & Book2.xls simultaneously open,
with source data to be compared in Sheet1's cols A and B from row2 down
with key col = col A (6 digit number)

In Book1.xls,
In C2:
=IF(A2="","",MATCH(A2,[Book2.xls]Sheet1!$A:$A,0))
C2 copied down to last row of data in col A
Non matching items will be flagged #N/A

In Book2.xls,
In C2:
=IF(A2="","",MATCH(A2,[Book1.xls]Sheet1!$A:$A,0))
C2 copied down to last row of data in col A
Non matching items will be flagged #N/A

Then for each of the above, similarly autofilter on col C for #N/A, and copy
n paste results elsewhere

---