Find values in One Column based on Other Columns

Question

I hope someone can help. I have an excel sheet with over 65,000 rows. I have attached a picture of what I am trying to achieve

Column J and N are the important ones as you can see in cell J2 a payment of 800 was made and in and in cell N2 you can see it was made on 11/16/2015. In cell J3 you can see we took back the money -800 and it was taken back on 1/4/2016 cell N3.

Is there a way or some logic or VBA code where I can easily identify these payments made in 2015 and taken back in 2016. Can I use the number to say what ever reverts back to zero show me, or can I use the columns D and L in yellow to identify these amounts. Scrolling through 65,000 lines will make me blind trying to identify these amounts any help would be greatly appreciated.

Sub Macro2()
Dim lastrow As Long
Dim lastColumn As Long
Application.ScreenUpdating = False

lastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
lastrow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "2"
Range("A2:A3").Select
Selection.AutoFill Destination:=Range("A2:A" & lastrow)
Range("A1").Value = "Row ID"
Columns("Q:Q").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("Q1").Select
ActiveCell.FormulaR1C1 = "positive identifier"
Columns("R:R").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("R1").Select
ActiveCell.FormulaR1C1 = "Matching row ID"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=1*AND(RC[-6]>0,YEAR(RC[-1])=2015)"
Range("Q2").Select
Selection.Style = "Comma"
Selection.AutoFill Destination:=Range("Q2:Q" & lastrow)
For i = 2 To lastrow
For j = 3 To lastrow
If Cells(i, 5).Value = Cells(j, 5).Value And Cells(i, 11).Value > 0 And Cells(i, 11).Value = -1 * Cells(j, 11).Value And Year(Cells(i, 16).Value) = 2015 And Year(Cells(j, 16).Value) = 2016 Then
Cells(i, 18).Value = j - 1
End If
Next
Next
Range("R2:R" & lastrow).Select
Selection.Style = "Comma"
Application.ScreenUpdating = True
End Sub

Got some code but its bugging out on

If Cells(i, 5).Value = Cells(j, 5).Value And Cells(i, 11).Value > 0 And Cells(i, 11).Value = -1 * Cells(j, 11).Value And Year(Cells(i, 16).Value) = 2015 And Year(Cells(j, 16).Value) = 2016 Then

the error that appears is Any help is greatly appreciated

Answer 1

I notice you are ok with sorting your data. This makes your problem a bit easier. This answer is not to provide a better formula, but to ease the pain of matching.

I thought for a while, and realize the combination of exceptional cases are too many. Even though it is not impossible to find the perfect formula but you will just waste too much effort in finding it. Lets just continue using the "flawed" formula to auto highlight the rows.

Horizontal lines

Notice that I turned off grid lines. And added the horizontal purple lines that split the customers apart. The purple lines are not manually drawn but a conditional formatting:-

Just apply this rule to the entire worksheet:

So instead of going through 65,000 lines, now its about how many customers you have on the list.

Formulas

• Q: =N(AND(J2>0,YEAR(N2)=2015))
• R: =N(AND(J2<0,YEAR(N2)=2016))
• S: =SUMIFS(Q:Q,D:D,D:D,L:L,L:L) + SUMIFS(R:R,D:D,D:D,L:L,L:L)
• T: =N(AND(SUMIFS(J:J,D:D,D:D,L:L,L:L,S:S,S:S)=0,S:S>=2))

This is what i have, hope someone can come up with better answers. Its interesting to solve big data problem anyway.

Answer 2

You can nest a MATCH function within an INDEX function.

The MATCH function is going to find the row number that matches a certain criteria, and the INDEX function finds the cell that matches the row number that the MATCH function (nested within) finds.

I hope this helps: http://www.randomwok.com/excel/how-to-use-index-match/

Answer 3

Here is the how the final output looks like. I use a combination of logic functions, some helper columns and of course the conditional formatting. Some of the columns are hidden because I just want to mimic your format and they are not used in my answer. I sorted my data by Customer ID and then by Nature, but you don't have to, I'll explain more later.

First, the formulas

Column Q, from Q2 down

=N(AND(J2>0,YEAR(N2)=2015))

Column R, from R2 down

=N(AND(J2<0,YEAR(N2)=2016))

Column S

=N(SUMIFS(Q:Q,D:D,D:D,L:L,L:L)+SUMIFS(R:R,D:D,D:D,L:L,L:L)=2)

Column T

=N(AND(SUMIFS(J:J,D:D,D:D,L:L,L:L,S:S,S:S)=0,S:S=1))

Next, conditional formatting

1. Select the columns you want to highlight with conditional formatting.

2. Create new conditional formatting rule

3. Make the rule as shown below, and press OK.

That is it. You should get the result like the first image above.

Precaution

You have 65,000 rows of data, I cannot rule out the four formulas above will fail to work in certain cases. So here are some heads up:

1. If customer paid twice and claim once for the same "Nature", then the formula fails, and the rows will not highlight. If this frequently occurs in your data, sorry, you have to extend on the logics to consider this. Perhaps use different color to conditionally highlight them?
2. (hmm... let me think, if there is more i'll add. Please comment.)

Performance

65,000 could take a toll on calculation time for the SUMIFS formula. I didn't test this and your computer could be much better than mind. Anyway if its becomes very laggy when you apply the formulas, then my suggestion is

1. Sort your data, by Customer ID and by Nature

2. And reduce the calculation range for SUMIFS in column S and T.

For example for column S, cell S4 formula become

=N(SUMIFS(Q2:Q6,D2:D6,D4,L2:L6,L4)+SUMIFS(R2:R6,D2:D6,D4,L2:L6,L4)=2)

That is to effectively reduce the calculation range to speed up performance.

Nature issue with customer id 18801591