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

[Ravi Arora]

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

 

[Aladin Akyurek]

In C2 enter and copy down:

=COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,-B2)>0

 

[Trebor76]

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

 

[Ravi Arora]

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

 

[Ravi Arora]

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

 

[Aladin Akyurek]

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

https://www.mrexcel.com/challenges/accounts-receivable-challenge/

 

[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

 

[Aladin Akyurek]

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)

 

[sarthak1287]

Thank you Aladin, exactly what I was looking for.

 

[Aladin Akyurek]

Thank you Aladin, exactly what I was looking for.

Great. You are welcome.