finding differences between 2 worksheets

L

Louise

I have a worksheet consisting of various information, including invoice
numbers. I receive this worksheet weekly and each week, some invoices will
have been paid so they won't appear on the next copy of the worksheet.

what is the easiest way of asking Excel to show those invoices which no
longer appear, which means they have been paid?

Any urgent help would be appreciated.

Thank you
Louise
 
A

Allllen

add a new column to the previous "old" sheet, and use vlookup against those
invoices against the column with invoice numbers from the new sheet.

Anything that is not found will return the #N/A value. You can use
autofilter or sort to group all these together.


***old book.xls***
***Old sheet***
Column A Column B
Invoice number Lookup
0001 =VLOOKUP(A2,'[new book.xls]new sheet'!A:B,2,false)
0002 =VLOOKUP(A3,'[new book.xls]new sheet'!A:B,2,false)
0003 =VLOOKUP(A4,'[new book.xls]new sheet'!A:B,2,false)
0004 =VLOOKUP(A5,'[new book.xls]new sheet'!A:B,2,false)
etc

***newbook.xls***
***New sheet***
Column A
Invoice number
0002
0004


The result that this will give you will look like this
***old book.xls***
***Old sheet***
Column A Column B
Invoice number Lookup
0001 #N/A
0002 0002
0003 #N/A
0004 0004
etc

Now use data > filter > autofilter, or just sort old book by column A.
 
L

Louise

That's great, thanks very much for your reply. I did consider the vlookup
but wondered if I was looking into it in more detail than was necessary.

Thanks again.
Louise

Allllen said:
add a new column to the previous "old" sheet, and use vlookup against those
invoices against the column with invoice numbers from the new sheet.

Anything that is not found will return the #N/A value. You can use
autofilter or sort to group all these together.


***old book.xls***
***Old sheet***
Column A Column B
Invoice number Lookup
0001 =VLOOKUP(A2,'[new book.xls]new sheet'!A:B,2,false)
0002 =VLOOKUP(A3,'[new book.xls]new sheet'!A:B,2,false)
0003 =VLOOKUP(A4,'[new book.xls]new sheet'!A:B,2,false)
0004 =VLOOKUP(A5,'[new book.xls]new sheet'!A:B,2,false)
etc

***newbook.xls***
***New sheet***
Column A
Invoice number
0002
0004


The result that this will give you will look like this
***old book.xls***
***Old sheet***
Column A Column B
Invoice number Lookup
0001 #N/A
0002 0002
0003 #N/A
0004 0004
etc

Now use data > filter > autofilter, or just sort old book by column A.


--
Allllen


Louise said:
I have a worksheet consisting of various information, including invoice
numbers. I receive this worksheet weekly and each week, some invoices will
have been paid so they won't appear on the next copy of the worksheet.

what is the easiest way of asking Excel to show those invoices which no
longer appear, which means they have been paid?

Any urgent help would be appreciated.

Thank you
Louise
Click to expand...
Click to expand...
 
Top