Find Unmatched Positive & Negative Numbers In A Column subject to customer ID
Results 1 to 10 of 10

Thread: Find Unmatched Positive & Negative Numbers In A Column subject to customer ID

  1. #1
    New Member
    Join Date
    Sep 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find Unmatched Positive & Negative Numbers In A Column subject to customer ID

    Hi Team, Please help me in solving the following. In Customer ID 5 we have three amount if there is unmatched positive and negative number is there in same amount then result should be true else false. Please help in solving this.

    Customer ID Amount Result
    5 100 True
    5 90 False
    5 -100 True
    6 50 True
    6 -50 True
    6 60 False
    7 90 True
    7 20 False
    7 -90 True

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Find Unmatched Positive & Negative Numbers In A Column subject to customer ID

    In C2 enter and copy down:

    =COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,-B2)>0
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,370
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Find Unmatched Positive & Negative Numbers In A Column subject to customer ID

    Hi Aladin,

    Thanks for this - I will recommend as a solution for this near identical thread where I used to code to provide a less then optimal solution for.

    Regards,

    Robert

  4. #4
    New Member
    Join Date
    Sep 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Unmatched Positive & Negative Numbers In A Column subject to customer ID

    Hi Aladin,

    Thanks for the solution. But one concern in this, In amount column Debit amount of Rs.100 is showing twice & credit amount of Rs.100 is showing once under the same customer ID. Then result against all 3 rows are showing true instead of 1st and 2nd row result should be True & third row result should be false. I mean same amount of credit entry should be knockoff once only.

    Customer ID Amount Result
    5 100 True
    5 -100 True
    5 100 True/False

  5. #5
    New Member
    Join Date
    Sep 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Unmatched Positive & Negative Numbers In A Column subject to customer ID

    Hi Robert,

    Thanks for the solution, what i want here is in A column customer id is mentioned, in B column invoice wise amount mentioned, same amount of invoice may be there twice or thrice under the same ID. In B column payment is also mentioned with negative value under the same ID. So i want to highlight those invoices against we have received the payment. On FIFO Basis amount need to knockoff. Please help in this.
    Customer ID Amount Result
    5 100 True
    5 -100 True
    5 100 False
    5 50 True
    5 -50 True
    5 40 False
    5 50 False
    6 70 False
    6 70 False

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Find Unmatched Positive & Negative Numbers In A Column subject to customer ID

    I think your actual question is more like the one expressed in:

    https://www.mrexcel.com/challenges/a...ble-challenge/
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member
    Join Date
    Sep 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Unmatched Positive & Negative Numbers In A Column subject to customer ID

    Thanks Aladin for your reply, Yes my question is more similiar to the question expressed. Only change is we are getting Invoice wise payment from the customer. So we need to knockoff credit payment with debit invoice only once.

    Thanks,
    Ravi

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Find Unmatched Positive & Negative Numbers In A Column subject to customer ID

    Quote Originally Posted by Ravi Arora View Post
    Thanks Aladin for your reply, Yes my question is more similiar to the question expressed. Only change is we are getting Invoice wise payment from the customer. So we need to knockoff credit payment with debit invoice only once.

    Thanks,
    Ravi
    In that case, in C2 enter and copy down:

    =COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)<=COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,-B2)
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    New Member
    Join Date
    Jun 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Unmatched Positive & Negative Numbers In A Column subject to customer ID

    Thank you Aladin, exactly what I was looking for.

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Find Unmatched Positive & Negative Numbers In A Column subject to customer ID

    Quote Originally Posted by sarthak1287 View Post
    Thank you Aladin, exactly what I was looking for.
    Great. You are welcome.
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •