Prev: Excel corrupts when asking to update vlookups
Next: For safety
From: Casper on 20 Apr 2010 00:16
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
From: Eduardo on 20 Apr 2010 07:59
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
From: Duke Carey on 20 Apr 2010 11:58
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
 | 
Pages: 1
Prev: Excel corrupts when asking to update vlookups
Next: For safety