remove / calculate negative values

liaosy

Board Regular
Joined
Apr 27, 2005
Messages
77
hi everyone,

i have the following customer names and amounts:

A B
Customer A 99.00
Customer A 15.00
Customer A 45.00
Customer A -100.00
Customer B 5000.00
Customer B -25.00
Customer C 100.00
Customer C 123.00
Customer D 100.00

I want to remove the negative values from this list by subtracting them from the first positive values that appears for each customer until the negative value becomes zero.

If the absolute value of the negative amount is greater than the first positive value for the customer, then continuing subtracting in the next row for the same customer.

Do this for each customer. How can this be done?

Thanks everyone!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
If you have a list of distinct customers in D from D2 on downwards and the data is A2:B9...

In E2 enter & copy down:

=SUMIF($A$2:$A$9,D2,$B$2:$B$9)
 

liaosy

Board Regular
Joined
Apr 27, 2005
Messages
77
keep list itemized and update

hi aladin,

thanks for suggestion.

but i am trying to keep the list itemized but simply setting all negative field values to zero while subtracting the negative amounts from the postive ones (starting fro the top down per customer).

if i run a sumif it yields a single sum total whiel disregarding the itemization by row.

the end result i am trying to get is:

A B
Customer A 0
Customer A 45.00
Customer A 0
Customer B 12.00
Customer B 100.00
Customer B 0

where the zeroes represent all the previously negative values (last row of each cutomer) and the zeroes in the top rosw represent the postiive vales that were used to subtract the negative values.

does this make sense?

thanks a.
 

liaosy

Board Regular
Joined
Apr 27, 2005
Messages
77
next step?

ok - since there are no -takers - let me try to work through the next step...

if i insert an empty row between each unique customer group, i can work with the top and bottom values more easily.

there needs to be an if statement that says if the absolute value of the negative amount is > than the first positive value then add the first positive value to the next positive value in R[1]C AND set the first row value to zero.

a loop needs to run in case the absolute value of the last row/negative value is still greater than the first two rows / positive values...

a second loop needs to run for each item in the collection to remove the negative values from all customer rows.

can anyone help produce the code for this? thanks!

=)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,915
Messages
5,575,018
Members
412,634
Latest member
Sumanmathew
Top