Forum Discussion

mathetes's avatar
mathetes
Gold Contributor
Nov 05, 2020

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.

  • IDRIS_A's avatar
    IDRIS_A
    Copper Contributor
    Nov 06, 2020

    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. 

    • mathetes's avatar
      mathetes
      Gold Contributor
      Nov 06, 2020

      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's avatar
        IDRIS_A
        Copper Contributor
        Nov 07, 2020

        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.

    • MindreVetande's avatar
      MindreVetande
      Iron Contributor
      Nov 06, 2020

      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

Resources