Thread: Find Unmatched Positive & Negative Numbers In A Column subject to customer ID Thanks:  4 Post #5149454 (2)Post #5149460 (1)Post #5152717 (1) Likes:  1 Post #5149454 (1)

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

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

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. Re: Find Unmatched Positive & Negative Numbers In A Column subject to customer ID

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

7. 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. Re: Find Unmatched Positive & Negative Numbers In A Column subject to customer ID

Originally Posted by Ravi Arora
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)

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

Thank you Aladin, exactly what I was looking for.

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

Originally Posted by sarthak1287
Thank you Aladin, exactly what I was looking for.
Great. You are welcome.

User Tag List

Posting Permissions

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