Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
creating a reconciling list of items not matched between two files
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
creating a reconciling list of items not matched between two files
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Oldersox" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating a filtered list | Excel Discussion (Misc queries) | |||
how do i create a drop down list of items from a different file | Excel Worksheet Functions | |||
How do I delete items in one list from another list? | Excel Discussion (Misc queries) | |||
Recently Used File List - 2002 Contains 'Temp' Files | Excel Discussion (Misc queries) | |||
How do I find out what items are in one list but not in another l. | Excel Discussion (Misc queries) |