Selected Answer
The code below will do what you have described. I have filled in, best guess, what you didn't describe which is what should happen to other data in the same rows. Sticking with your sample, data in columns B:D are moved down relative to everything else on the sheet. Take a good look at the enumeration at the top of the code. That is where you can make significant changes. Also look at the name of the worksheet. I used "Alpha" because I found "Sheet1" too generic. A good, descriptive name will go a long way toward preventing the code being run unintentionally on data you don't want changed.
Option Explicit
Enum Nws ' worksheet navigation
' 10 Jan 2018
' ==== Set1End determines the last column to
' be moved down together with Name1
' Observe that columns left of Name1 will not be moved
NwsFirstDataRow = 8 ' adjust as required
NwsName1 = 2 ' 2 = column B
NwsSet1End = 4
NwsName2 ' 5 = column E
End Enum
Sub CompareAndAlignNames()
' 10 Jan 2018
Dim Ws As Worksheet
Dim Rng As Range
Dim Arr1() As Variant, Arr2() As Variant
Dim R As Long, R1 As Long
Dim i As Long, j As Long
Set Ws = Worksheets("Alpha") ' name the sheet with the name lists on them
With Ws
' write nothing below data in columns Name1 and Name2 !
R = .Cells(.Rows.Count, NwsName2).End(xlUp).Row
Set Rng = Range(.Cells(NwsFirstDataRow, NwsName2), .Cells(R, NwsName2))
Arr2 = Rng.Value
R = .Cells(.Rows.Count, NwsName1).End(xlUp).Row
Set Rng = Range(.Cells(NwsFirstDataRow, NwsName1), .Cells(R, NwsSet1End))
Arr1 = Application.Transpose(Rng.Value)
End With
For R = 1 To UBound(Arr2)
R1 = R1 + 1
If StrComp(Arr2(R, 1), Arr1(1, R1), vbTextCompare) Then
ReDim Preserve Arr1(LBound(Arr1) To UBound(Arr1), _
LBound(Arr1, 2) To UBound(Arr1, 2) + 1)
For i = UBound(Arr1, 2) To (R1 + 1) Step -1
For j = LBound(Arr1) To UBound(Arr1)
Arr1(j, i) = Arr1(j, i - 1)
Next j
Next i
For j = LBound(Arr1) To UBound(Arr1)
Arr1(j, i) = ""
Next j
End If
Next R
With Rng
.Cells(1).Resize(UBound(Arr1, 2), UBound(Arr1)).Value = Application.Transpose(Arr1)
End With
End Sub
There is a working example in the attached workbook.
