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

Ravi Arora

New Member
Joined
Sep 25, 2018
Messages
4
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Ravi Arora

New Member
Joined
Sep 25, 2018
Messages
4
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
Joined
Sep 25, 2018
Messages
4
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
Joined
Sep 25, 2018
Messages
4
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
Joined
Feb 14, 2002
Messages
85,179
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,002
Messages
5,484,134
Members
407,432
Latest member
Suldarion

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top