Forum Discussion

Copper Contributor

Nov 05, 2020

SUM TO ZERO

Dear Users, I have over 6000 data and i want to Match all amount that sums to zero or a set value without the try and error method which takes time and prone to errors, please how can i achieve t...

Formulas and Functions

Macros and VBA

power bi

Riny_van_Eekelen

Platinum Contributor

Nov 06, 2020

IDRIS_A Similar to mtarler's solution and with the same end-result (I believe), something I picked-up a while ago from https://www.myonlinetraininghub.com/excel-bank-reconciliation-formula

I've taken the liberty to apply this trick to your data set, using a few helper columns and an intermediate pivot table. It highlights all numbers that cancel each other out. Whether these cancelling numbers really are related is something for you to determine.

SAMPLE-5.xlsx115 KB

IDRIS_A
Copper Contributor
Nov 07, 2020
thank you very much. Riny_van_Eekelen , suppose i have a distinguish matching details just like @mtarler' have helped asked, how should i go about it? thanks for your contributions.
- Riny_van_Eekelen
  Platinum Contributor
  Nov 07, 2020
  IDRIS_A Add another helper column adding the attribute/description that pairs matching opposites. Build the pivot table using the 2nd helper column and refresh. Made up some attributes to test it. See attached.
  SAMPLE-5.xlsx131 KB