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

Ravi Arora

New Member
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
 

Ravi Arora

New Member
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

New Member
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
 

Ravi Arora

New Member
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

MrExcel MVP
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)
 

Some videos you may like

This Week's Hot Topics

Top