How do I compare in between two worksheets?
 

I have two following worksheets:
Name of worksheet: Billing
NAME WEEK ENDING QTY PRICE AMOUNT STATUS
Chris 5/10/2005 40.0 10.00 400.00
Chris 5/10/2005 5.0 15.00 75.00
Robert 5/10/2005 35.0 12.00 420.00
John 5/15/2005 40.0 15.00 600.00
John 5/15/2005 5.0 30.00 150.00
Tom 5/15/2005 35.0 10.00 350.00
Chris 5/20/2005 40.0 10.00 400.00

Name of worksheet: Payment
NAME WEEK ENDING QTY PRICE AMOUNT PAYMENT STATUS
Chris 5/10/2005 40.0 10.00 400.00 405.00
Robert 5/10/2005 40.0 10.00 400.00 400.00
John 5/15/2005 35.0 15.00 525.00 520.00
Easter 5/15/2005 40.0 20.00 800.00 800.00
Chris 5/20/2005 40.0 10.00 400.00 402.00

I need to compare the four lists (name, Week ending, qty, price) of PAYEMNT
with BILLING and matching the exact or closest data. Also, this result should
return status column in both worksheets as follows:

Status:1. Paid as Billed
2. Over Paid
3. Under Paid
4. Not Paid
5. Not Billed

How do I do this?
Please help me........ I am looking for your help

Don't use two worksheets. Use one worksheet to combine the two, using copy
and paste. Use another column, with Bill or Payment in it, depending on
what the data is. Change the billed amounts to negative values. Then use a
pivot table, and you will get an accurate summary with no formulas that will
allow you to compare your received amounts with your billed amounts.

HTH,
Bernie
MS Excel MVP


"korman" wrote in message
...
I have two following worksheets:
Name of worksheet: Billing
NAME WEEK ENDING QTY PRICE AMOUNT STATUS
Chris 5/10/2005 40.0 10.00 400.00
Chris 5/10/2005 5.0 15.00 75.00
Robert 5/10/2005 35.0 12.00 420.00
John 5/15/2005 40.0 15.00 600.00
John 5/15/2005 5.0 30.00 150.00
Tom 5/15/2005 35.0 10.00 350.00
Chris 5/20/2005 40.0 10.00 400.00

Name of worksheet: Payment
NAME WEEK ENDING QTY PRICE AMOUNT PAYMENT STATUS
Chris 5/10/2005 40.0 10.00 400.00 405.00
Robert 5/10/2005 40.0 10.00 400.00 400.00
John 5/15/2005 35.0 15.00 525.00 520.00
Easter 5/15/2005 40.0 20.00 800.00 800.00
Chris 5/20/2005 40.0 10.00 400.00 402.00

I need to compare the four lists (name, Week ending, qty, price) of
PAYEMNT
with BILLING and matching the exact or closest data. Also, this result
should
return status column in both worksheets as follows:

Status:1. Paid as Billed
2. Over Paid
3. Under Paid
4. Not Paid
5. Not Billed

How do I do this?
Please help me........ I am looking for your help