Match Debits and credits?

A

allie357

In my previous posts I was asking about counting unique invoice
numbers and Purchase Order numbers (PO's). Well, now I have a new
problem. I have to go through and evaluate the invoice numbers and
po's and eliminate any matching debits and credits from my count. This
may or may not affect including an invoice and po in my count. Many
invoices can be included on one po. What type of formula do I use to
match the debits and credits of an invoice number?

MILTENYI BIOTEC INC 16-Aug-07 PO 0721868 778184 $455.00
MILTENYI BIOTEC INC 16-Aug-07 PO 0721868 778184 ($492.00)
MILTENYI BIOTEC INC 16-Aug-07 PO 0721868 778184 $455.00
MILTENYI BIOTEC INC 16-Aug-07 PO 0721868 778184 $492.00
MILTENYI BIOTEC INC 16-Aug-07 PO 0721868 $37.00
MILTENYI BIOTEC INC 16-Aug-07 PO 0721868 778184 ($455.00)

MILTENYI BIOTEC INC 16-Aug-07 PO 0721963 777976 ($550.00)
MILTENYI BIOTEC INC 16-Aug-07 PO 0721963 $37.00
MILTENYI BIOTEC INC 16-Aug-07 PO 0721963 777976 $500.00
MILTENYI BIOTEC INC 16-Aug-07 PO 0721963 777976 $550.00
MILTENYI BIOTEC INC 21-Aug-07 PO 0722270 $37.00
MILTENYI BIOTEC INC 21-Aug-07 PO 0722270 778660 $520.00
MILTENYI BIOTEC INC 21-Aug-07 PO 0722271 $62.00
MILTENYI BIOTEC INC 21-Aug-07 PO 0722271 779439 $530.00
 
A

Ajay

In my previous posts I was asking about counting unique invoice
numbers and Purchase Order numbers (PO's). Well, now I have a new
problem. I have to go through and evaluate the invoice numbers and
po's and eliminate any matching debits and credits from my count. This
may or may not affect including an invoice and po in my count. Many
invoices can be included on one po. What type of formula do I use to
match the debits and credits of an invoice number?

MILTENYI BIOTEC INC 16-Aug-07 PO 0721868 778184 $455.00
MILTENYI BIOTEC INC 16-Aug-07 PO 0721868 778184 ($492.00)
MILTENYI BIOTEC INC 16-Aug-07 PO 0721868 778184 $455.00
MILTENYI BIOTEC INC 16-Aug-07 PO 0721868 778184 $492.00
MILTENYI BIOTEC INC 16-Aug-07 PO 0721868 $37.00
MILTENYI BIOTEC INC 16-Aug-07 PO 0721868 778184 ($455.00)

MILTENYI BIOTEC INC 16-Aug-07 PO 0721963 777976 ($550.00)
MILTENYI BIOTEC INC 16-Aug-07 PO 0721963 $37.00
MILTENYI BIOTEC INC 16-Aug-07 PO 0721963 777976 $500.00
MILTENYI BIOTEC INC 16-Aug-07 PO 0721963 777976 $550.00
MILTENYI BIOTEC INC 21-Aug-07 PO 0722270 $37.00
MILTENYI BIOTEC INC 21-Aug-07 PO 0722270 778660 $520.00
MILTENYI BIOTEC INC 21-Aug-07 PO 0722271 $62.00
MILTENYI BIOTEC INC 21-Aug-07 PO 0722271 779439 $530.00
Click to expand...

Can you send the excel file then it would easy for us to help you.
 
S

Socko

Make sure that you are clear in explaining the logic so that somebody
can straightaway put efforts to help you rather than taking a lot of
time to know what exactly you mean to say?


I hope this helps.

Selva V Pasupathy
For more on Excel, VBA, & other resources
Please visit http://socko.wordpress.com/
 
A

allie357

Vendor Name Accounting Date Category InvoiceNum PO Number Amount
MILTENYI BIOTEC INC 25-Feb-08 PO 0805035 823894 $650.00
MILTENYI BIOTEC INC 25-Feb-08 PO 0805035 823894 $355.00
MILTENYI BIOTEC INC 25-Feb-08 PO 0805035 $49.00
MILTENYI BIOTEC INC 25-Feb-08 PO 0805035 823894 $355.00
MILTENYI BIOTEC INC 26-Feb-08 PO 0805091 823975 $575.00
MILTENYI BIOTEC INC 26-Feb-08 PO 0805091 $39.00
MILTENYI BIOTEC INC 03-Apr-08 PO 0805277 $29.00
MILTENYI BIOTEC INC 03-Apr-08 PO 0805277 824371 $355.00
MILTENYI BIOTEC INC 01-Mar-08 PO 0805294 ($5.80)
MILTENYI BIOTEC INC 01-Mar-08 PO 0805294 ($5.80)
MILTENYI BIOTEC INC 01-Mar-08 PO 0805294 ($5.80)
MILTENYI BIOTEC INC 01-Mar-08 PO 0805294 636705 ($131.00)
MILTENYI BIOTEC INC 26-Feb-08 PO 0805294 636705 $131.00
MILTENYI BIOTEC INC 01-Mar-08 PO 0805294 ($5.80)
MILTENYI BIOTEC INC 01-Mar-08 PO 0805294 ($5.80)
MILTENYI BIOTEC INC 26-Feb-08 PO 0805294 $5.80
MILTENYI BIOTEC INC 26-Feb-08 PO 0805294 636705 $131.00
MILTENYI BIOTEC INC 01-Mar-08 PO 0805294 636705 ($131.00)
MILTENYI BIOTEC INC 26-Feb-08 PO 0805294 $5.80
MILTENYI BIOTEC INC 26-Feb-08 PO 0805294 $5.80
MILTENYI BIOTEC INC 26-Feb-08 PO 0805294 636705 $131.00
MILTENYI BIOTEC INC 26-Feb-08 PO 0805294 $5.80
MILTENYI BIOTEC INC 26-Feb-08 PO 0805294 636705 $131.00
MILTENYI BIOTEC INC 26-Feb-08 PO 0805294 636705 $131.00
MILTENYI BIOTEC INC 01-Mar-08 PO 0805294 636705 ($131.00)
MILTENYI BIOTEC INC 26-Feb-08 PO 0805294 $5.80
MILTENYI BIOTEC INC 01-Mar-08 PO 0805294 636705 ($131.00)
MILTENYI BIOTEC INC 01-Mar-08 PO 0805294 636705 ($131.00)

I need to count the unique invoice numbers and PO's. As you can see
there are many invoices per po. I need to match debits and credits by
po number to see if those need to be eliminated from my count or not.
 
Top