Excel VBA: Sum invoice by client id with copying result to new worksheet

Question

Hi, i have strange problem doing reporting:

i have numerous clients with different issued invoices. Problem comes to the point when there are invoices in minus and plus:

Column A consists of client unique IDs, Column B invoice number, column C invoice amount

A          | B          | C 

0010019991 | 1800149471 | 162.00 | 2010-03-12

0010019991 | 1800136388 | 162.00 | 2010-02-12

0010019991 | 1600008004 | -36.00 | 2010-03-15

0010021791 | 1800132148 | 162.00 | 2010-03-12

0010021791 | 1800145436 | 162.00 | 2010-02-12

0010021791 | 1600007737 | -12.00 | 2010-03-15

0014066147 | 1800119068 | 1,684.80 | 2010-03-12

0014066147 | 1800123702 | 1,684.80 | 2010-02-12

0014066147 | 1600007980 | -1,300.80 | 2010-02-15

0014066147 | 1600007719 | -1,286.40 | 2010-03-15

I need to remove rows with negative invoices in a way that amount is summed with invoices which are not with negative amount. So that final result would look like:

A          | B          | C      | D

0010019991 | 1800149471 | 126.00 | 2010-03-12

0010019991 | 1800136388 | 162.00 | 2010-02-12

0010021791 | 1800132148 | 150.00 | 2010-03-12

0010021791 | 1800145436 | 162.00 | 2010-02-12

0014066147 | 1800123702 | 782.40 | 2010-02-12

Answer 1

In columns G:J create a list of all your Client ID's, Invoice numbers, and date combinations for positive rows (assuming you don't have multiple lines for each invoice, this can be done by copy-pasting A:D in G:J, sorting by Dollars, deleting the negative ones, then resorting by Customer ID).

(columns G:J must be sorted by Customer ID) Then in Column F put (starting with F2):

=SUMPRODUCT(--(G2=A$1:A$10),--(H2=B$1:B$10),--(J2=D$1:D$10),C$1:C$10)+IF(G2<>G1,SUMPRODUCT(--(G2=A$1:A$10),--(C$1:C$10<0),C$1:C$10))

The first half of finds all lines with matching customer ID, invoice, and date and adds them up. The second half, if this lines Customer ID is not equal to the previous line, than it must be the first line for that customer, so include all the negative dollar amounts for that customer ID regardless of date and Invoice number.

Now in Column I, where you're going to have the dollar values that you actually want, starting in I2 put:

=Max(0,F2)

Then in I3 (and drag downward) Put

=MAX(SUMPRODUCT(--(G3=G$1:G3),F$1:F3)-SUMPRODUCT(--(G3=G$1:G2),I$1:I2),0)

What this does is first checks to see how many dollars have been attributed to that customer above (and including) your current row from column F. Then subtracts all the dollars already attributed to the current customer above your current row in column I. These numbers are than forced to be at least 0. This method effectively carries down any negative amounts to the line bellow. You will end up with:

F      G        H          I     J           
126    10019991 1800149471 126    2010-03-12 
162    10019991 1800136388 162    2010-02-12 
150    10021791 1800132148 150    2010-03-12 
162    10021791 1800145436 162    2010-02-12 
-902.4 14066147 1800119068 0      2010-03-12 
1684.8 14066147 1800123702 782.4  2010-02-12 

You will still have some 0'd lines about, if you want those gone, copy G:J to another sheet using paste values, sort by dollars and remove all the 0's then resort again by customer ID.

Note that any Customers with a total negative dollar amount will just come in as all 0's.