Fahim has attended:
Excel Intermediate course
Effective Communication Skills course
Offsetting positive & negative values on 1 column
I have over 50,000 numbers (positive and negative) in 1 column on a dataset and I want to find offsetting values clearing each other. Is there a way to find this please.
RE: Offsetting positive & negative values on 1 column
Hi Fahim
This is a good question Fahim and likely is connected to your need to reconcile accounting/bank statement information
There is no one formula for the task and why there is specialist accounting software to match these numbers at volume
You could sort the data so that all positive values are in one column and all negative values are in column two. You can convert a negative to a positive using =-B2 (if B2 is negative then negative and negative make a positive number )
You could then sort both columns in ascending order and match them up using an IF function =IF(A2=B2,"MATCH", "NO")
I hope that gets you started on solving this
Kind regards
Richard
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
