Identifying Certain Values in Columns Excel

[Conell8383]

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
The problem I am facing is that i have a worksheet with 65,000 rows, and I have payments and reimbursements withing these rows. In the below screen shot you can see that customer Adsay Fahrettin, customer id 17706421 was paid 800 euro Column J cell J2 and that payment was made on 11/16/2015 Column N Cell N2.
Then customer Adsay Fahrettin, customer id 17706421 has the 800 taken back from him Column J cell J3 and that transaction happened on 1/4/2016 Column N Cell N3.

There are 100's more of these transactions in the worksheet how can is there a way to identify?
Any help would be greatly appreciated

 

[bhos123]

=AND(j2<0,year(O2)=2016) may be you can use this.. to get all -ves in 2016.

 

[Conell8383]

Hi. Again thank you for the help. I found that =AND(J2<0,YEAR(N2)=2016) worked better. Is there a way now to highlight or identify the cells or rows that have TRUE FALSE in Column O and FALSE TRUE in Column P

 

[bhos123]

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



Run this Code

 

[Conell8383]

Thank you for taking the time to write the code. unfortunately the sheet begins to hang and the below error pops up. is there anything i can do to fix this? Again thank you so much for the help

 

[bhos123]

It was running perfectly to me. In which line you are getting errors?

 

[Conell8383]

The line i get the errors on is
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">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</code>

 

[bhos123]

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("P:P").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("P1").Select
ActiveCell.FormulaR1C1 = "positive identifier"
Columns("Q:Q").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Matching row ID"
Range("P2").Select
ActiveCell.FormulaR1C1 = "=1*AND(RC[-6]>0,YEAR(RC[-1])=2015)"
Range("P2").Select
Selection.Style = "Comma"
Selection.AutoFill Destination:=Range("P2:P" & 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, 15).Value) = 2015 And Year(Cells(j, 15).Value) = 2016 Then
Cells(i, 17).Value = j - 1
End If
Next
Next
Range("Q2:Q" & lastrow).Select
Selection.Style = "Comma"
Application.ScreenUpdating = True
End Sub


Hope this works.

 

[Conell8383]

Hi, Thank you again for the help.
So I removed all the formatting from the Excel sheet and ran the first piece of code again and it kind of worked there is just a slight issue.

When the Macro runs I think it is looking in Column P which it thinks its the date but Column O is the Date and then the whole thing fails. I have attached a picture. If I change the formula in the in the Q Column from =1*AND(K2>0,YEAR(P2)=2015) to =1*AND(K2>0,YEAR(O2)=2015) I get a 1 result and a - result but then nothing is happening in the R Column which is where I suspect the answer is. Can the FIRST piece of code be modified to accommodate the columns?

Before i make the column change i get #VALUE!

After I make the column Change

Again thank you for the help. It is greatly appreciated.

 

[bhos123]

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(1*RC[-6]>0,YEAR(RC[-2])=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 1 * Cells(i, 11).Value > 0 And 1 * Cells(i, 11).Value = -1 * Cells(j, 11).Value And Year(Cells(i, 15).Value) = 2015 And Year(Cells(j, 15).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


Not sure why this is happening. Hope this works.

 

[Conell8383]

excel - Find values in One Column based on Other Columns - Stack Overflow
The Answer is here
Hi All, I Hope this helps someone. For Identifying certain columns. I went with =AND(J2>0,YEAR(N2)=2015) tell you "True" or "False" if a payment in 2015 column N was greater than 0 column J. Then I went =AND(J2<0,YEAR(N2)=2016) tell you "True" or "False" if a payment in 2016 column N was less than 0 column J. To make the formula work i then went. putting these two formulas after the N column will give you a new O and P column after these put in =N(AND(J2>0,YEAR(N2)=2015)) and =N(AND(J2<0,YEAR(N2)=2016)) pretty much the same as above but makes them in to a number this will give you a new Q and R Column then use =SUMIFS(Q:Q,D:D,D:D,L:L,L:L) + SUMIFS(R:R,D:D,D:D,L:L,L:L) this will give you a new S column and from there you use =N(AND(SUMIFS(J:J,D:D,D:D,L:L,L:L,S:S,S:S)=0,S:S>=2)) in Column T
I hope the below pic helps