Summing up opposite numbers in a column based on criteria....

**rshukla** · 06-10-2009, 05:51 PM

Imagine column A sorted by invoice numbers. Column B has payments applied but due to movement between unapplied cash, some numbers appear as negatives and positives for the same invoice number.

I want to have a formula which says that for each invoice number, look in column b and eliminate opposite numbers and post only unique values. Keep in mind that there may be multiple lines with the same invoice number.

For example, starting with line 5, for invoice 123, payments 400 and -400 in line 5 and 17 and payments 315 and -315 in lines 7 and 9 will zero out but any other unique payment will be picked up and displayed in column C. Lines 5 and/or 17 will be blank in column C.

Can anybody help please!

Thank you.

**NBVC** · 06-11-2009, 08:38 AM

See attached for a suggested solution:

Formula in C2:

Please Login or Register  to view this content.

copied down...

This formula counts the number of "opposites" to the invoice and amount on the line and also determines if there is an even number or odd number of absolute values of that entry up to that point... so that it will blank out the first positve and second negative or vice versa, but not the third (if there is no fourth).

For example, there are 3 entries of invoice Invoice/Acct # 123 with amounts 400, -400 and 400... only the first 400 and the -400 will be blanked, the 3rd will remain.. (unless a 4th opposite comes up).

**rshukla** · 06-11-2009, 04:18 PM

NBVC,

Thank you so much for your reply. That formula is very close to what I need. I have finally figured out how to attach excel files to the message. Please review the attached file. It is a sample of the actual data I am using. I have added a column where it shows the desired result of the formula. I hope it helps.

Some observations about the data;

- Invoice numbers (Col. A) are not scattered. They always appear together as if the data itself was sorted by Col. A.

- There may be any number of combinations of those pesky positive and negative amounts. Example (in different rows): -400, -400, -400, 400, 400, 400, -300 OR -400, 400, 100, -400, -400, 400. You will see some stuff like this in the attached file.

But thanks a lot for all your help already. I'd be a very happy camper if you could please fix this for me.

Thanks!

**NBVC** · 06-12-2009, 08:38 AM

Try:

Please Login or Register  to view this content.

in G2, copied down.

Remember to adjust ranges to suit your actual data if larger/smaller...

**rshukla** · 06-12-2009, 08:47 PM

NBVC,

Sorry for the delayed reply. Busy day. I am loving your formula. Later tonight I will try it on a 35,000 line file. I am not expecting any issues.

Will update you tomorrow. Thank you very much! I am not bad at excel, but I couldn't have figured this one out to save my life. My boss is delighted!

Thanks again!

**rshukla** · 06-17-2009, 11:20 AM

NBVC,

I have checked it on a huge file and compared the output with our Accounting system. It was a huge database and it took me a while.

There is only one glitch left to fix in the formula. Please review the attached file (rows 189 to 196 & 199 to 204). The formula is giving a blank result for positive and negative amounts showing up in the following combination;

IN-SJ3-108128184 - 1,590.00
IN-SJ3-108128184 - 1,590.00
IN-SJ3-108128184 - 1,590.00
IN-SJ3-108128184 - (1,590.00)
IN-SJ3-108128184 - (1,590.00)
IN-SJ3-108128184 - (1,590.00)
IN-SJ3-108128184 - (1,590.00)

In other words, it is not picking up an amount if the positive and negative amounts are the same dollar amount and grouped together.

Thank you.

**rshukla** · 06-17-2009, 12:53 PM

Invoice no. IN-SJ3-108128184 mistakenly appears in two separate places in the file. I was trying to check a different combination of those positive and negative amounts. Please see attached file... I changed the last digit from 4 to 5.

There's 3 test cases in the file to fix.

I am wondering if the formula is designed to check only a certain number of rows for the amount to be picked up. If so, any way we can change that a higher number of rows? I am trying to understand the formula so just ignore this post if not applicable. If I can help in any way, please let me know.

Thank you for all your help and I'm sorry this is taking so much work.

**NBVC** · 06-17-2009, 02:25 PM

See attached.

I used some Named Ranges to determine the individual group of like invoice numbers.

To create the Named Ranges...

Go to G2 and then go to Insert|Name|Define

Enter C_Start in Names in this workbook field and enter formula:

Please Login or Register  to view this content.

Then click Ok.

Re-invoke Insert|Name|Define and enter C_End in Names in this workbook with formula:

Please Login or Register  to view this content.

Then Click Ok.

In G2, Enter formula:

Please Login or Register  to view this content.

and copy down.

Note: The named ranges are dynamic allowing you to add more accounts to the bottom with adjusting formulas.. you just need to copy formula in G down..

Hope this does the trick.... it took a bit to think this one through.

**rshukla** · 06-23-2009, 11:24 AM

Testing... testing...

Everything is looking great so far! Variances are gone! Boss, this is magic...

Testing...

**rshukla** · 06-29-2009, 01:14 AM

NBVC,

Your formula works wonderfully and I have had no variances in my data. You really did put in a lot of work in this problem and I am really thankful.

- Rahul.