SUM TO ZERO

%3CLINGO-SUB%20id%3D%22lingo-sub-1856333%22%20slang%3D%22en-US%22%3ESUM%20TO%20ZERO%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1856333%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Users%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20over%206000%20data%20and%20i%20want%20to%20Match%20all%20amount%20that%20sums%20to%20zero%20or%20a%20set%20value%20without%20the%20try%20and%20error%20method%20which%20takes%20time%20and%20prone%20to%20errors%2C%20please%20how%20can%20i%20achieve%20that%20using%20excel%20or%20Macros.%3C%2FP%3E%3CP%3Ethanks.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1856333%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1856956%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20TO%20ZERO%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1856956%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F836508%22%20target%3D%22_blank%22%3E%40IDRIS_A%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20you're%20asking%20is%20not%20exactly%20clear%2C%20I'm%20sorry%20to%20say.%20Nevertheless%2C%20if%20what%20you%20mean%20by%20%22Match%22%20is%20to%20identify%2C%20then%20you%20could%20use%20the%20Filter%20capability.%20That's%20available%20under%20the%20Data...toolbar.%20Or%2C%20if%20you%20have%20the%20newest%20versions%20of%20Excel%2C%20there's%20a%20FILTER%20function%20that%20could%20list%20every%20row%20of%20a%20database%20where%20zero%20is%20identified%20in%20some%20field.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20would%20be%20far%20more%20likely%20to%20get%20a%20more%20detailed%20and%20helpful%20response%20if%20you%20could%20post%20your%20actual%20spreadsheet%20(just%20making%20sure%20it%20contains%20no%20confidential%20information).%20A%20simple%20verbal%20description%20is%20almost%20always%20too%20confusing%20or%20unclear.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1860217%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20TO%20ZERO%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1860217%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%20Thanks%20for%20your%20response.%20yes%20it%20is%20to%20identify%2C%20attached%20shows%20a%20random%20sample%20which%20i%20am%20sure%20sum%20of%20the%20highlighted%20is%20equal%20to%20zero%20(which%20is%20what%20i%20want%20to%20achieve).%3C%2FP%3E%3CP%3Esuppose%20i%20have%206000%20or%20more%20cells%20cells%20to%20identify%20.%20how%20best%20can%20i%20go%20about%20it.%20i%20hope%20this%20clarify%20my%20questions.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1860282%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20TO%20ZERO%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1860282%22%20slang%3D%22en-US%22%3EIn%20attached%20Sheet%20Column%20A%20has%20few%20values%20are%20Negative%20and%20few%20are%20positive%2C%2C%2C%20and%20if%20you%20are%20thinking%20to%20use%20SUM%2C%2C%2C%20then%20you%20are%20not%20supposed%20to%20get%20ZERO%2C%2C%2C%20please%20edit%20your%20post%20and%20be%20clear%20and%20loud%2C%20or%20show%20us%20the%20expected%20results%20in%20attached%20sheet%2C%2C%2C%20honesty%20I'm%20clueless%20!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1861258%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20TO%20ZERO%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1861258%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F836508%22%20target%3D%22_blank%22%3E%40IDRIS_A%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20correct%20that%20the%20highlighted%20cells%20do%20sum%20to%20zero.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20raises%20at%20least%20a%20number%20of%20questions%3A%3C%2FP%3E%3CUL%3E%3CLI%3EHow%20did%20you%20identify%20them%3F%20(Or%20was%20this%20just%20a%20made-up%20set%20where%20you%20simply%20changed%20the%20signs%3F)%3C%2FLI%3E%3CLI%3EPresumably%20every%20number%20in%20those%20highlighted%20ranges%20(or%20the%20vast%20majority%20of%20them)%20has%20a%20matching%20number%3A%3CUL%3E%3CLI%3Edo%20you%20want%20therefore%20to%20identify%3CUL%3E%3CLI%3Eevery%20pair%20that%20sums%20to%20zero%2C%3C%2FLI%3E%3CLI%3Eevery%20set%20of%20four%20that%20sums%20to%20zero%2C%3C%2FLI%3E%3CLI%3Eevery%20set%20of%20six%20that%20sums%20to%20zero%2C%3C%2FLI%3E%3CLI%3Eetc%3C%2FLI%3E%3CLI%3EOr%20just%20the%20largest%20set%20(as%20you%20have%20in%20your%20sample)%3C%2FLI%3E%3CLI%3E%3F%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20to%20become%20a%20lot%20clearer.....%3C%2FP%3E%3CP%3Eand%20%3CU%3E%3CEM%3E%3CSTRONG%3Emaybe%20you%20could%20describe%20the%20context%20you're%20working%20in%20here%20as%20well%2C%20so%20long%20as%20it's%20not%20confidential.%20Maybe%20there's%20a%20different%20way%20to%20meet%20the%20objective.%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1861427%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20TO%20ZERO%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1861427%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F836508%22%20target%3D%22_blank%22%3E%40IDRIS_A%3C%2FA%3E%26nbsp%3B%20as%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%20(and%20others)%20have%20said%20we%20need%20more%20information%20to%20understand%20exactly%20what%20you%20want%2Fneed.%26nbsp%3B%20But%20looking%20at%20the%20number%20it%20almost%20appears%20that%20you%20have%20a%20ledger%20entries%20and%20are%20trying%20to%20find%20values%20that%20don't%20match%20up%20and%20need%20to%20be%20investigated.%26nbsp%3B%20So%20in%20that%20case%20or%20similar%20you%20want%20to%20have%20paired%20values%20highlighted%20(or%20ignored).%26nbsp%3B%20Here%20is%20a%20conditional%20formatting%20formula%20you%20can%20use%20to%20highlight%20any%20value%20that%20does%20not%20have%20a%20corresponding%20matched%20but%20opposite%20value.%20Note%20that%20it%20will%20highlight%20the%20FIRST%20x%20number%20of%20occurrences%20of%20that%20value%20so%20if%20you%20have%205%2C%205%2C%20-5%2C%205%2C%205%2C%20-5%2C%20-5%20it%20will%20highlight%20the%20FIRST%205%20and%20not%20the%20following%205s%20or%20-5s%20because%20those%20have%20'matches'.%26nbsp%3B%20BUT%20if%20this%20is%20a%20ledger%20and%20you%20want%20to%20know%20the%20unmatched%20line%20item%20you%20may%20need%20additional%20info%20to%20distinguish%20which%205%20and%20-5%20actually%20match%20up.%26nbsp%3B%20AGAIN%2C%20we%20need%20more%20info%20to%20fully%20help.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20Conditional%20Formatting%20formula%20I%20mentioned%20and%20used%20in%20the%20attached%20sheet%20to%20highlight%20the%20UNmatched%20values%20in%20RED%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D((COUNTIF(A%3AA%2CA1)-COUNTIF(A%3AA%2C-A1))%26gt%3B%3DCOUNTIF(%24A%241%3AA1%2CA1))*ISNUMBER(A1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 that using excel or Macros.

thanks. 

11 Replies

@IDRIS_A 

 

What you're asking is not exactly clear, I'm sorry to say. Nevertheless, if what you mean by "Match" is to identify, then you could use the Filter capability. That's available under the Data...toolbar. Or, if you have the newest versions of Excel, there's a FILTER function that could list every row of a database where zero is identified in some field.

 

You would be far more likely to get a more detailed and helpful response if you could post your actual spreadsheet (just making sure it contains no confidential information). A simple verbal description is almost always too confusing or unclear.

@mathetes  Thanks for your response. yes it is to identify, attached shows a random sample which i am sure sum of the highlighted is equal to zero (which is what i want to achieve).

suppose i have 6000 or more cells cells to identify . how best can i go about it. i hope this clarify my questions. 

In attached Sheet Column A has few values are Negative and few are positive,,, and if you are thinking to use SUM,,, then you are not supposed to get ZERO,,, please edit your post and be clear and loud, or show us the expected results in attached sheet,,, honesty I'm clueless !!

@IDRIS_A 

Do you want to find/markup any positive number that has a matching negative number (and any negative number with a positive match)? Try something like this i B2 

=Countif(A:A,-A2)

And copy down.

0 there is no match with opposite sign

1 there is one match with opposite sign

>1 (ambivalent)There is at least one match with opposite sign, but the match might be "used" by another number with the same sign

 

Try some kind of IF statement to sort things out

=if(countif(A:A,-A2)=1,"pair",if(Countif(A:A,-A2)=0,"alone",IF(COUNTIF(A:A,-A2)=Countif(A:A,A2),"Even pairs")))

 

PS

I'm using a non-english excel and i translating in my head, Check the spelling of the formulas

/DS

@IDRIS_A 

 

You're correct that the highlighted cells do sum to zero.

 

That raises at least a number of questions:

  • How did you identify them? (Or was this just a made-up set where you simply changed the signs?)
  • Presumably every number in those highlighted ranges (or the vast majority of them) has a matching number:
    • do you want therefore to identify
      • every pair that sums to zero,
      • every set of four that sums to zero,
      • every set of six that sums to zero,
      • etc
      • Or just the largest set (as you have in your sample)
      • ?

 

You need to become a lot clearer.....

and maybe you could describe the context you're working in here as well, so long as it's not confidential. Maybe there's a different way to meet the objective.

 

@IDRIS_A  as  @mathetes  (and others) have said we need more information to understand exactly what you want/need.  But looking at the number it almost appears that you have a ledger entries and are trying to find values that don't match up and need to be investigated.  So in that case or similar you want to have paired values highlighted (or ignored).  Here is a conditional formatting formula you can use to highlight any value that does not have a corresponding matched but opposite value. Note that it will highlight the FIRST x number of occurrences of that value so if you have 5, 5, -5, 5, 5, -5, -5 it will highlight the FIRST 5 and not the following 5s or -5s because those have 'matches'.  BUT if this is a ledger and you want to know the unmatched line item you may need additional info to distinguish which 5 and -5 actually match up.  AGAIN, we need more info to fully help.  

Here is the Conditional Formatting formula I mentioned and used in the attached sheet to highlight the UNmatched values in RED:

=((COUNTIF(A:A,A1)-COUNTIF(A:A,-A1))>=COUNTIF($A$1:A1,A1))*ISNUMBER(A1)

@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.

@mathetes  thank you very much, i appreciate your responses.

 

  • i identified them manually .
  • they have a matching number, it is a debit and credit , vice versa.
  • every pair that sum to zero or the largest .
  • I'm working on a ledger with a debit and credit. 

thanks for your time.

@mtarler thanks for your response.

 

you are right, you have put the question better than i have asked . it is a ledger entries . if i can get additional line that distinguish them (the ones that should match) , how best should i go about it, thanks. 

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.

@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.