# 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

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

##### MrExcel MVP
In C2 enter and copy down:

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

#### Trebor76

##### Well-known Member

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

##### New Member

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

##### 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)

#### sarthak1287

##### New Member
Thank you Aladin, exactly what I was looking for.

##### MrExcel MVP
Thank you Aladin, exactly what I was looking for.
Great. You are welcome.

1,089,970
Messages
5,411,579
Members
403,380
Latest member
ifog671