I have made a macro for similar function before. You can try and see if this suits your needs.
Sub Sort()
'Sorts data from column B onwards, number of columns of data is determined by first row (Header)
'Reads column A, searches column B, then sorts accordingly to reference in column A
'If what in column A is not found in column B, 1 blank row will be inserted.
Dim lastc As Integer
Dim notfoundcount As Double
Dim r As Long
Dim X As Integer
Dim Y As Long
lastc = 2
lastc = ActiveCell.SpecialCells(xlLastCell).Column
Dim last As Double
last = ActiveCell.SpecialCells(xlLastCell).row
notfoundcount = 0
Application.ScreenUpdating = False
For loopread = 2 To last
If Range("A" + CStr(loopread)).FormulaR1C1 <> "" Then
If Trim(Range("A" + CStr(loopread)).FormulaR1C1) <> Trim(Range("B" + CStr(loopread)).FormulaR1C1) Then
For loopsearch = loopread To (last + notfoundcount)
If Trim(Range("B" + CStr(loopsearch)).FormulaR1C1) = Trim(Range("A" + CStr(loopread)).FormulaR1C1) And loopsearch <> loopread Then
Range("B" + CStr(loopsearch) + ":" + GetCol(lastc) + CStr(loopsearch)).Cut
Range("B" + CStr(loopread)).Insert Shift:=xlDown
Exit For
End If
If loopsearch = (last + notfoundcount) Then
'not found, insert blank row and highlight red
Application.CutCopyMode = False
Range("B" + CStr(loopread) + ":" + GetCol(lastc) + CStr(loopread)).Insert Shift:=xlDown
Range("B" + CStr(loopread) + ":" + GetCol(lastc) + CStr(loopread)).Interior.Color = 255
notfoundcount = notfoundcount + 1
End If
Next
End If
End If
Next
Application.ScreenUpdating = True
End Sub
Public Function GetCol(ByVal Coln As Integer) As String
Dim times As Integer
Dim col1 As Integer
If Coln > 256 Then Exit Function
If Coln > 26 Then
times = Int(Coln / 26)
col1 = Coln - times * 26
If Not col1 = 0 Then
GetCol = Chr(64 + times) & Chr(64 + col1)
Else
GetCol = Chr(64 + times - 1) & Chr(90)
End If
Else
GetCol = Chr(64 + Coln)
End If
End Function
Edit: this was made very long ago before I discovered about Cells() object/method, so I was using a separate function to convert Column numbers to alphabets
Bookmarks