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

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

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