|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 20th, 2010, 05:16 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reconciling Invoice & Credit
Hi there, can somebody please help. Below is the data table I have. On the
4th column "Status", I need to know which invoices remain outstanding and which ones cancel off (squares with Credit amount) and returns the Doc reference # (if any) on the fifth column. Will appreciate very much if you can teach me a quick way to do it. Customer Doc No. Amount Status Ref Susan Inv3355 893.10 Cancels off Cre1222 Nelly Inv3356 250.14 Cancels off Cre1221 Tom Inv3357 462.91 Cancels off Cre1223 Nelly Cre1221 -250.14 Cancels off Inv3356 Nelly Inv3358 350.14 Outstanding Susan Cre1222 -893.10 Cancels off Inv3355 Susan Inv3359 793.10 Outstanding Tom Cre1223 -462.91 Cancels off Inv1223 Tom Inv3360 462.91 Outstanding Burt Cre1224 101.99 Outstanding -- Many thanks, Casper 
|
|
#2
April 20th, 2010, 12:59 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reconciling Invoice & Credit
Hi,
not sure what you need, the reference # in the 5th column from where?, if you want just to see the oustanding invoices you can filter column 4 by oustanding "Casper" wrote: Hi there, can somebody please help. Below is the data table I have. On the 4th column "Status", I need to know which invoices remain outstanding and which ones cancel off (squares with Credit amount) and returns the Doc reference # (if any) on the fifth column. Will appreciate very much if you can teach me a quick way to do it. Customer Doc No. Amount Status Ref Susan Inv3355 893.10 Cancels off Cre1222 Nelly Inv3356 250.14 Cancels off Cre1221 Tom Inv3357 462.91 Cancels off Cre1223 Nelly Cre1221 -250.14 Cancels off Inv3356 Nelly Inv3358 350.14 Outstanding Susan Cre1222 -893.10 Cancels off Inv3355 Susan Inv3359 793.10 Outstanding Tom Cre1223 -462.91 Cancels off Inv1223 Tom Inv3360 462.91 Outstanding Burt Cre1224 101.99 Outstanding -- Many thanks, Casper
|
|
#3
April 20th, 2010, 04:58 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reconciling Invoice & Credit
Doing this if you were certain to have only unique combinations of Customer
and Amount would be one thing, but what about these cases: you have the same customer with multiple OUTSTANDING invoices of the identical amount, or you get partial payments in on an invoice "Casper" wrote: Hi there, can somebody please help. Below is the data table I have. On the 4th column "Status", I need to know which invoices remain outstanding and which ones cancel off (squares with Credit amount) and returns the Doc reference # (if any) on the fifth column. Will appreciate very much if you can teach me a quick way to do it. Customer Doc No. Amount Status Ref Susan Inv3355 893.10 Cancels off Cre1222 Nelly Inv3356 250.14 Cancels off Cre1221 Tom Inv3357 462.91 Cancels off Cre1223 Nelly Cre1221 -250.14 Cancels off Inv3356 Nelly Inv3358 350.14 Outstanding Susan Cre1222 -893.10 Cancels off Inv3355 Susan Inv3359 793.10 Outstanding Tom Cre1223 -462.91 Cancels off Inv1223 Tom Inv3360 462.91 Outstanding Burt Cre1224 101.99 Outstanding -- Many thanks, Casper
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
