| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I compare data in two worksheets to find matching cells? | Excel Discussion (Misc queries) | |||
| How do I compare two Excel worksheets for cell differences? | Excel Discussion (Misc queries) | |||
| How can I compare worksheets in the same workbook? | Excel Discussion (Misc queries) | |||
| Compare 2 Worksheets Create a 3rd depending on results | Excel Discussion (Misc queries) | |||
| compare columns of different worksheets | Excel Discussion (Misc queries) | |||
Linear Mode
