Compare & align like items from 2 Roaster columns of Employees.
..
For instance :
Old in Column A New in Column B
Albert Albert
Bob Bob
Charles Dwight
Dwight Elmer
Frank Gus
..
Expected Result after VBA execution
Old in Column A New in Column B
Albert Albert
Bob Bob
Charles
Dwight Dwight
Elmer
Frank
Gus
..
The following solution given on this group is close to working,
but, it has a bug I have not been able to resolve in debug mode.
Namely, in the loop process, it finds Elmer, but either does not
write it or overwrites it.
In addition, I would like to see the syntax for Old Roaster coming
from Workbook A Sheet1
New Roaster from Workbook B Sheet1, and the result in Workbook C
Sheet1.
..
Sub LineEmUp()
Dim flag As Boolean
Dim MyRangeA As Range, MyRangeC As Range
Columns("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = lastrow To 2 Step -1
If IsEmpty(Cells(x - 1, 1)) Or IsEmpty(Cells(x, 1)) Then GoTo
getmeout
If Asc(UCase(Cells(x, 1))) - Asc(UCase(Cells(x - 1, 1))) 1 Then
For p = 1 To (Asc(Cells(x, 1)) - Asc(Cells(x - 1, 1))) - 1
Rows(x).Select
Selection.Insert shift:=xlDown
Next
getmeout:
End If
Next
'sort B
Columns("B:B").Insert shift:=xlToRight
lastrowC = Cells(Rows.Count, "C").End(xlUp).Row
lastrowA = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRangeC = Range("C1:C" & lastrowC)
Set MyRangeA = Range("A1:A" & lastrowA)
For Each c In MyRangeC
For Each a In MyRangeA
flag = True
If UCase(a.Value) = UCase(c.Value) Then
a.Offset(, 1).Value = c.Value
flag = False
Exit For
End If
Next
If flag = True Then
templast = Cells(Rows.Count, "B").End(xlUp).Row
Range("A" & templast + 1).Offset(, 1).Value = c.Value
flag = False
End If
Next
'Tidy Up
Columns("C:C").Delete shift:=xlToLeft
Columns("A:B").Sort Key1:=Range("A1"), Order1:=xlAscending
lastrowA = Cells(Rows.Count, "A").End(xlUp).Row
lastrowB = Cells(Rows.Count, "B").End(xlUp).Row
For x = WorksheetFunction.Max(lastrowA, lastrowB) To 1 Step -1
If IsEmpty(Cells(x, 1)) And IsEmpty(Cells(x, 2)) Then
Rows(x).EntireRow.Delete
End If
Next
End Sub
..
This case has many applications like in scheduling to detect either
new or dropped activities.
Thank you for your help.
J.P.