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

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top