Sub SortData()
Dim arr1() As Variant, arr2() As Integer, i As Integer
lr1 = Range("B" & Rows.Count).End(xlUp).Row
lr2 = Range("E" & Rows.Count).End(xlUp).Row
'Copy qua cot AA
Range("AA1:AA" & lr1 - 4).Value = Range("B5:B" & lr1).Value
Range("AA" & lr1 - 3 & ":AA" & lr1 + lr2 - 8).Value = Range("E5:E" & lr2).Value
'Remove dupplicate va sort
Range("AA1:AA" & lr1 + lr2 - 8).RemoveDuplicates Columns:=1, Header:=xlNo
lr3 = Range("AA" & Rows.Count).End(xlUp).Row
Worksheets("Sheet1").Sort.SortFields.Clear
Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("AA1:AA" & lr3), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Worksheets("Sheet1").Sort
.SetRange Range("AA1:AA" & lr3)
.Apply
End With
'Cap nhat data
ReDim arr1(1 To lr3)
ReDim arr2(1 To lr3)
For i = 1 To lr3
Set c = Range("B5:B" & lr1).Find(Range("AA" & i).Value, LookIn:=xlValues)
If Not c Is Nothing Then
arr1(i) = Range("AA" & i).Value
End If
Set c = Range("E5:E" & lr2).Find(Range("AA" & i).Value, LookIn:=xlValues)
If Not c Is Nothing Then
arr2(i) = Range("AA" & i).Value
End If
Next
Range("B5:B" & lr3 - 4).Value = Application.WorksheetFunction.Transpose(arr1)
Range("E5:E" & lr3 - 4).Value = Application.WorksheetFunction.Transpose(arr2)
Range("AA1:AA" & lr3).ClearContents
End Sub
