Forum Discussion
Removing positive and negative values in excel
Hello.
I am currently dealing with an excel sheet where there is multiple vendors for which each contain innumerable positive and negative value transactions.
I would like a formula/shortcut that will enable me to match the (+/-) exact value but for the same vendor.
I don't wish to set off values for example, -100 under vendor 1 with +100 associated with vendor 2. My desire is to set off values but for the same vendor only.
Kindly assist
I am currently dealing with an excel sheet where there is multiple vendors for which each contain innumerable positive and negative value transactions.
I would like a formula/shortcut that will enable me to match the (+/-) exact value but for the same vendor.
I don't wish to set off values for example, -100 under vendor 1 with +100 associated with vendor 2. My desire is to set off values but for the same vendor only.
Kindly assist
4 Replies
Thomas610 I believe your need is to match e.g. individual vendor invoice amounts (credits) with individual payments made (debits) to these individual vendor invoices. The attached workbook contains an example for such a situation. It's inspired by this (rather old) article, but it still does the job:
https://www.myonlinetraininghub.com/excel-bank-reconciliation-formula
I have adapted it a little so that it matches amounts by vendor.
- I didn't understand you properly.
One way is having absolute values using the ABS() formula.
If you can share some sample data and what your desirable output would be good for us to assist you better.- Kindly direct me to a link where I can retrieve the ABS approach. If this doesn't help I'll revert
- ABS() formula converts negative numbers into positive numbers.
But, with sample data would be better to understand your doubt.
