Excel VBA combine common rows based of two columns place into another sheet

[jurjevics]

All,

I am looking for a quicker way to combine row with common information into another sheet based off of two columns.
First criteria is based of of Serial number (column A) and then a Premier End Date (column L). I have reports that have over 11k line of SN
Right now I am basing off of Serial number (column A)
It is very slow but any help would be greatly appreciated
See code below: part 1

Sub Asset_Correlation()
On Error GoTo Asset_Correlation_Error
Dim wsAsset_Correlation As Worksheet
Dim wsAMR_Asset_Report As Worksheet
Dim wsAsset_Correlation_lastrow As Long
Dim wsAMR_Asset_Report_lastrow As Long
Dim x As Integer
Dim y As Integer
Dim TmpACRow As Long
Dim TmpACColumn As Long
Dim TmpACSN As String
Dim TmpACString As String
Dim TmpACString1 As String
Dim TmpARRow As Long
Dim TmpARRow2 As Long
Dim TmpARRow3 As Long
Dim TmpARSN As String
Dim TmpARString As String
Call unlock_sheets
Set wb = ActiveWorkbook
Set wsAsset_Correlation = wb.Worksheets("Asset Correlation")
Set wsAMR_Asset_Report = wb.Worksheets("AMR Asset Report")
Set wsDrop_Downs = wb.Worksheets("Drop Downs")
wsAMR_Asset_Report_lastrow = wsAMR_Asset_Report.Cells(wsAMR_Asset_Report.Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For TempColumndatecheckColumn = 11 To 21
If TempColumndatecheckColumn <> 16 Then
For TempColumndatecheckRow = 5 To wsAMR_Asset_Report_lastrow
Date_String = wsAMR_Asset_Report.Cells(TempColumndatecheckRow, TempColumndatecheckColumn).Value
If Date_String = "" Or Date_String = " " Then
wsAMR_Asset_Report.Cells(TempColumndatecheckRow, TempColumndatecheckColumn).Value = ""
Else
Current_Date = CDate(Date_String)
wsAMR_Asset_Report.Cells(TempColumndatecheckRow, TempColumndatecheckColumn).Value = Current_Date
End If
Next TempColumndatecheckRow
End If
Next TempColumndatecheckColumn
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
'wsAMR_Asset_Report.Range("A4").CurrentRegion.Sort Key1:=wsAMR_Asset_Report.Range("A4"), Order1:=xlAscending, Header:=xlYes
wsAMR_Asset_Report.Range("L4").CurrentRegion.Sort Key1:=wsAMR_Asset_Report.Range("L4"), Order1:=xlDescending, Header:=xlYes
wsAsset_Correlation_lastrow = wsAsset_Correlation.Cells(wsAsset_Correlation.Rows.Count, "A").End(xlUp).Row
wsAMR_Asset_Report_lastrow = wsAMR_Asset_Report.Cells(wsAMR_Asset_Report.Rows.Count, "L").End(xlUp).Row
wsAsset_Correlation.Range("B5:AE" & wsAsset_Correlation_lastrow).ClearContents
x = 1
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If wsAMR_Asset_Report_lastrow <> 0 Then
For TmpACRow = 5 To wsAsset_Correlation_lastrow
TmpACSN = wsAsset_Correlation.Cells(TmpACRow, 1).Value
For TmpARRow = 5 To wsAMR_Asset_Report_lastrow
If x < 2 Then
TmpARSN = wsAMR_Asset_Report.Cells(TmpARRow, 1).Value
TmpARString = wsAMR_Asset_Report.Cells(TmpARRow, 11).Value
If TmpACSN = TmpARSN And TmpARString <> "" And TmpARString <> " " Then
'B to L
wsAsset_Correlation.Cells(TmpACRow, 2).Value = wsAMR_Asset_Report.Cells(TmpARRow, 2).Value
wsAsset_Correlation.Cells(TmpACRow, 3).Value = wsAMR_Asset_Report.Cells(TmpARRow, 3).Value
wsAsset_Correlation.Cells(TmpACRow, 4).Value = wsAMR_Asset_Report.Cells(TmpARRow, 4).Value
wsAsset_Correlation.Cells(TmpACRow, 5).Value = wsAMR_Asset_Report.Cells(TmpARRow, 5).Value
wsAsset_Correlation.Cells(TmpACRow, 6).Value = wsAMR_Asset_Report.Cells(TmpARRow, 6).Value
wsAsset_Correlation.Cells(TmpACRow, 7).Value = wsAMR_Asset_Report.Cells(TmpARRow, 7).Value
wsAsset_Correlation.Cells(TmpACRow, 8).Value = wsAMR_Asset_Report.Cells(TmpARRow, 8).Value
wsAsset_Correlation.Cells(TmpACRow, 9).Value = wsAMR_Asset_Report.Cells(TmpARRow, 9).Value
wsAsset_Correlation.Cells(TmpACRow, 10).Value = wsAMR_Asset_Report.Cells(TmpARRow, 10).Value
wsAsset_Correlation.Cells(TmpACRow, 11).Value = wsAMR_Asset_Report.Cells(TmpARRow, 11).Value
wsAsset_Correlation.Cells(TmpACRow, 12).Value = wsAMR_Asset_Report.Cells(TmpARRow, 12).Value
'W to AB
wsAsset_Correlation.Cells(TmpACRow, 23).Value = wsAMR_Asset_Report.Cells(TmpARRow, 23).Value
wsAsset_Correlation.Cells(TmpACRow, 24).Value = wsAMR_Asset_Report.Cells(TmpARRow, 24).Value
wsAsset_Correlation.Cells(TmpACRow, 25).Value = wsAMR_Asset_Report.Cells(TmpARRow, 25).Value
wsAsset_Correlation.Cells(TmpACRow, 26).Value = wsAMR_Asset_Report.Cells(TmpARRow, 26).Value
wsAsset_Correlation.Cells(TmpACRow, 27).Value = wsAMR_Asset_Report.Cells(TmpARRow, 27).Value
wsAsset_Correlation.Cells(TmpACRow, 28).Value = wsAMR_Asset_Report.Cells(TmpARRow, 28).Value
x = x + 1
End If
End If
Next TmpARRow
x = 1
Next TmpACRow
End If
x = 1
'Column M to N
wsAMR_Asset_Report.Range("N4").CurrentRegion.Sort Key1:=wsAMR_Asset_Report.Range("N4"), Order1:=xlDescending, Header:=xlYes
wsAMR_Asset_Report_lastrow = wsAMR_Asset_Report.Cells(wsAMR_Asset_Report.Rows.Count, "N").End(xlUp).Row
If wsAMR_Asset_Report_lastrow <> 0 Then
For TmpACRow = 5 To wsAsset_Correlation_lastrow
TmpACSN = wsAsset_Correlation.Cells(TmpACRow, 1).Value
For TmpARRow = 5 To wsAMR_Asset_Report_lastrow
If x < 2 Then
TmpARSN = wsAMR_Asset_Report.Cells(TmpARRow, 1).Value
TmpARString = wsAMR_Asset_Report.Cells(TmpARRow, 2).Value
If TmpACSN = TmpARSN And TmpARString <> "" And TmpARString <> " " Then
wsAsset_Correlation.Cells(TmpACRow, 13).Value = wsAMR_Asset_Report.Cells(TmpARRow, 13).Value
wsAsset_Correlation.Cells(TmpACRow, 14).Value = wsAMR_Asset_Report.Cells(TmpARRow, 14).Value
TmpACString1 = wsAsset_Correlation.Cells(TmpACRow, 2).Value
If TmpACString1 = "" Or TmpACString1 = " " Then
'B to L
wsAsset_Correlation.Cells(TmpACRow, 2).Value = wsAMR_Asset_Report.Cells(TmpARRow, 2).Value
wsAsset_Correlation.Cells(TmpACRow, 3).Value = wsAMR_Asset_Report.Cells(TmpARRow, 3).Value
wsAsset_Correlation.Cells(TmpACRow, 4).Value = wsAMR_Asset_Report.Cells(TmpARRow, 4).Value
wsAsset_Correlation.Cells(TmpACRow, 5).Value = wsAMR_Asset_Report.Cells(TmpARRow, 5).Value
wsAsset_Correlation.Cells(TmpACRow, 6).Value = wsAMR_Asset_Report.Cells(TmpARRow, 6).Value
wsAsset_Correlation.Cells(TmpACRow, 7).Value = wsAMR_Asset_Report.Cells(TmpARRow, 7).Value
wsAsset_Correlation.Cells(TmpACRow, 8).Value = wsAMR_Asset_Report.Cells(TmpARRow, 8).Value
wsAsset_Correlation.Cells(TmpACRow, 9).Value = wsAMR_Asset_Report.Cells(TmpARRow, 9).Value
wsAsset_Correlation.Cells(TmpACRow, 10).Value = wsAMR_Asset_Report.Cells(TmpARRow, 10).Value

'W to AB
wsAsset_Correlation.Cells(TmpACRow, 23).Value = wsAMR_Asset_Report.Cells(TmpARRow, 23).Value
wsAsset_Correlation.Cells(TmpACRow, 24).Value = wsAMR_Asset_Report.Cells(TmpARRow, 24).Value
wsAsset_Correlation.Cells(TmpACRow, 25).Value = wsAMR_Asset_Report.Cells(TmpARRow, 25).Value
wsAsset_Correlation.Cells(TmpACRow, 26).Value = wsAMR_Asset_Report.Cells(TmpARRow, 26).Value
wsAsset_Correlation.Cells(TmpACRow, 27).Value = wsAMR_Asset_Report.Cells(TmpARRow, 27).Value
wsAsset_Correlation.Cells(TmpACRow, 28).Value = wsAMR_Asset_Report.Cells(TmpARRow, 28).Value
End If
x = x + 1
End If
End If
Next TmpARRow
x = 1
Next TmpACRow
End If
dating = True
x = 1
'Column O
wsAMR_Asset_Report.Range("O4").CurrentRegion.Sort Key1:=wsAMR_Asset_Report.Range("O4"), Order1:=xlDescending, Header:=xlYes
wsAMR_Asset_Report_lastrow = wsAMR_Asset_Report.Cells(wsAMR_Asset_Report.Rows.Count, "O").End(xlUp).Row
If wsAMR_Asset_Report_lastrow <> 0 Then
For TmpACRow = 5 To wsAsset_Correlation_lastrow
TmpACSN = wsAsset_Correlation.Cells(TmpACRow, 1).Value
For TmpACColumn = 15 To 15 ' Column O
For TmpARRow = 5 To wsAMR_Asset_Report_lastrow
TmpARSN = wsAMR_Asset_Report.Cells(TmpARRow, 1).Value

TmpARString = wsAMR_Asset_Report.Cells(TmpARRow, TmpACColumn).Value
If TmpACSN = TmpARSN And TmpARString <> "" And TmpARString <> " " Then
wsAsset_Correlation.Cells(TmpACRow, TmpACColumn).Value = wsAMR_Asset_Report.Cells(TmpARRow, TmpACColumn).Value
Exit For

End If
Next TmpARRow
Next TmpACColumn
Next TmpACRow
End If

[jurjevics]

Rest of the code


'Column P to R
wsAMR_Asset_Report.Range("R4").CurrentRegion.Sort Key1:=wsAMR_Asset_Report.Range("R4"), Order1:=xlDescending, Header:=xlYes
wsAMR_Asset_Report_lastrow = wsAMR_Asset_Report.Cells(wsAMR_Asset_Report.Rows.Count, "R").End(xlUp).Row

If wsAMR_Asset_Report_lastrow <> 0 Then

For TmpACRow = 5 To wsAsset_Correlation_lastrow

TmpACSN = wsAsset_Correlation.Cells(TmpACRow, 1).Value
For TmpARRow = 5 To wsAMR_Asset_Report_lastrow
If x < 2 Then
TmpARSN = wsAMR_Asset_Report.Cells(TmpARRow, 1).Value
TmpARString = wsAMR_Asset_Report.Cells(TmpARRow, 2).Value
If TmpACSN = TmpARSN And TmpARString <> "" And TmpARString <> " " Then
wsAsset_Correlation.Cells(TmpACRow, 16).Value = wsAMR_Asset_Report.Cells(TmpARRow, 16).Value
wsAsset_Correlation.Cells(TmpACRow, 17).Value = wsAMR_Asset_Report.Cells(TmpARRow, 17).Value
wsAsset_Correlation.Cells(TmpACRow, 18).Value = wsAMR_Asset_Report.Cells(TmpARRow, 18).Value
TmpACString1 = wsAsset_Correlation.Cells(TmpACRow, 2).Value
If TmpACString1 = "" Or TmpACString1 = " " Then

'B to L
wsAsset_Correlation.Cells(TmpACRow, 2).Value = wsAMR_Asset_Report.Cells(TmpARRow, 2).Value
wsAsset_Correlation.Cells(TmpACRow, 3).Value = wsAMR_Asset_Report.Cells(TmpARRow, 3).Value
wsAsset_Correlation.Cells(TmpACRow, 4).Value = wsAMR_Asset_Report.Cells(TmpARRow, 4).Value
wsAsset_Correlation.Cells(TmpACRow, 5).Value = wsAMR_Asset_Report.Cells(TmpARRow, 5).Value
wsAsset_Correlation.Cells(TmpACRow, 6).Value = wsAMR_Asset_Report.Cells(TmpARRow, 6).Value
wsAsset_Correlation.Cells(TmpACRow, 7).Value = wsAMR_Asset_Report.Cells(TmpARRow, 7).Value
wsAsset_Correlation.Cells(TmpACRow, 8).Value = wsAMR_Asset_Report.Cells(TmpARRow, 8).Value
wsAsset_Correlation.Cells(TmpACRow, 9).Value = wsAMR_Asset_Report.Cells(TmpARRow, 9).Value
wsAsset_Correlation.Cells(TmpACRow, 10).Value = wsAMR_Asset_Report.Cells(TmpARRow, 10).Value

'W to AB
wsAsset_Correlation.Cells(TmpACRow, 23).Value = wsAMR_Asset_Report.Cells(TmpARRow, 23).Value
wsAsset_Correlation.Cells(TmpACRow, 24).Value = wsAMR_Asset_Report.Cells(TmpARRow, 24).Value
wsAsset_Correlation.Cells(TmpACRow, 25).Value = wsAMR_Asset_Report.Cells(TmpARRow, 25).Value
wsAsset_Correlation.Cells(TmpACRow, 26).Value = wsAMR_Asset_Report.Cells(TmpARRow, 26).Value
wsAsset_Correlation.Cells(TmpACRow, 27).Value = wsAMR_Asset_Report.Cells(TmpARRow, 27).Value
wsAsset_Correlation.Cells(TmpACRow, 28).Value = wsAMR_Asset_Report.Cells(TmpARRow, 28).Value
End If
x = x + 1
End If
End If
Next TmpARRow
x = 1
Next TmpACRow
End If


x = 1

'Column S to T

wsAMR_Asset_Report.Range("T4").CurrentRegion.Sort Key1:=wsAMR_Asset_Report.Range("T4"), Order1:=xlDescending, Header:=xlYes
wsAMR_Asset_Report_lastrow = wsAMR_Asset_Report.Cells(wsAMR_Asset_Report.Rows.Count, "T").End(xlUp).Row


If wsAMR_Asset_Report_lastrow <> 0 Then
For TmpACRow = 5 To wsAsset_Correlation_lastrow
TmpACSN = wsAsset_Correlation.Cells(TmpACRow, 1).Value
For TmpACColumn = 19 To 20 ' Column S to T
For TmpARRow = 5 To wsAMR_Asset_Report_lastrow
TmpARSN = wsAMR_Asset_Report.Cells(TmpARRow, 1).Value

TmpARString = wsAMR_Asset_Report.Cells(TmpARRow, TmpACColumn).Value
If TmpACSN = TmpARSN And TmpARString <> "" And TmpARString <> " " Then
wsAsset_Correlation.Cells(TmpACRow, TmpACColumn).Value = wsAMR_Asset_Report.Cells(TmpARRow, TmpACColumn).Value
Exit For

End If
Next TmpARRow
Next TmpACColumn
Next TmpACRow
End If

'Column U

wsAMR_Asset_Report.Range("U4").CurrentRegion.Sort Key1:=wsAMR_Asset_Report.Range("U4"), Order1:=xlDescending, Header:=xlYes
wsAMR_Asset_Report_lastrow = wsAMR_Asset_Report.Cells(wsAMR_Asset_Report.Rows.Count, "U").End(xlUp).Row


If wsAMR_Asset_Report_lastrow <> 0 Then
For TmpACRow = 5 To wsAsset_Correlation_lastrow
TmpACSN = wsAsset_Correlation.Cells(TmpACRow, 1).Value
For TmpACColumn = 21 To 21 ' Column U
For TmpARRow = 5 To wsAMR_Asset_Report_lastrow
TmpARSN = wsAMR_Asset_Report.Cells(TmpARRow, 1).Value

TmpARString = wsAMR_Asset_Report.Cells(TmpARRow, TmpACColumn).Value
If TmpACSN = TmpARSN And TmpARString <> "" And TmpARString <> " " Then
wsAsset_Correlation.Cells(TmpACRow, TmpACColumn).Value = wsAMR_Asset_Report.Cells(TmpARRow, TmpACColumn).Value
Exit For

End If
Next TmpARRow
Next TmpACColumn
Next TmpACRow
End If

'Column V

wsAMR_Asset_Report.Range("V4").CurrentRegion.Sort Key1:=wsAMR_Asset_Report.Range("V4"), Order1:=xlDescending, Header:=xlYes
wsAMR_Asset_Report_lastrow = wsAMR_Asset_Report.Cells(wsAMR_Asset_Report.Rows.Count, "V").End(xlUp).Row


If wsAMR_Asset_Report_lastrow <> 0 Then
For TmpACRow = 5 To wsAsset_Correlation_lastrow
TmpACSN = wsAsset_Correlation.Cells(TmpACRow, 1).Value
For TmpACColumn = 22 To 22 ' Column V
For TmpARRow = 5 To wsAMR_Asset_Report_lastrow
TmpARSN = wsAMR_Asset_Report.Cells(TmpARRow, 1).Value

TmpARString = wsAMR_Asset_Report.Cells(TmpARRow, TmpACColumn).Value
If TmpACSN = TmpARSN And TmpARString <> "" And TmpARString <> " " Then
wsAsset_Correlation.Cells(TmpACRow, TmpACColumn).Value = wsAMR_Asset_Report.Cells(TmpARRow, TmpACColumn).Value
Exit For

End If
Next TmpARRow
Next TmpACColumn
Next TmpACRow
End If


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Call lock_sheets
wsAsset_Correlation.Activate
wsAsset_Correlation.Range("A5").Select
Exit Sub

Asset_Correlation_Error:
MsgBox "An error happen in Asset Correlation subroutine. Contact AMR Team!"
Call lock_sheets
End Sub

[jurjevics]

I uploaded a txt file of the code