Merge two columns with non-duplicates and sort

Hi again

This code is a little more general

Option Explicit

Sub MergeLists(wsName As String, List1 As Range, List2 As Range)
    Dim LastRowList1 As Long, LastRowList2 As Long
    Dim ws As Worksheet
        
    Set ws = Sheets("Sheet1")
    With ws
        LastRowList1 = .Cells(Rows.Count, List1.Column).End(xlUp).Row
        LastRowList2 = .Cells(Rows.Count, List2.Column).End(xlUp).Row
        
        .Range(.Cells(List1.Row, List1.Column), .Cells(LastRowList1, List1.Columns.Count + List1.Column - 1)).Copy
        .Cells(LastRowList2 + 1, List2.Column).PasteSpecial Paste:=xlPasteValues, _
                                              Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        If IsDate(Cells(List2.Row, List2.Column)) Then
            .Columns(List2.Column).NumberFormat = "m/d/yyyy"
        End If
        
        LastRowList2 = .Cells(Rows.Count, List2.Column).End(xlUp).Row
        
        Application.CutCopyMode = False
        
        
        Set List2 = .Range(.Cells(List2.Row, List2.Column), .Cells(LastRowList2, List2.Columns.Count + List2.Column - 1))
        List2.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
        
        LastRowList2 = .Cells(Rows.Count, List2.Column).End(xlUp).Row
    
        .Sort.SortFields.Clear
        
        .Sort.SortFields.Add Key:=.Range(.Cells(List2.Row, List2.Column).Address), _
                              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        '.Sort.SortFields.Add Key:=Range(.Cells(List2.Row, List2.Column + 1).Address), _
                              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SetRange .Range(.Cells(List2.Row, List2.Column), .Cells(LastRowList2, List2.Columns.Count + List2.Column - 1))
        .Sort.Header = xlNo
        .Sort.MatchCase = False
        .Sort.Orientation = xlTopToBottom
        .Sort.SortMethod = xlPinYin
        .Sort.Apply
    End With
End Sub

Sub MergeListsByColumnRange()
    MergeLists "Sheet1", Range("B:F"), Range("L:P")
End Sub

The previous code was not data specific that I could see
However after runing the code, "Remove Duplicates" seems to clear the cells of found duplicates of all formatting This seems to happen when the list is closed up.

Hence a number of rows after the new list is formed have no formatting.

The number of rows affected = number of rows before removal - number of rows after removal

To fix this in this instance I have reformatted column L in the code.

Because your concatinated names in the example sheet will not sort as you have indicated.

Lastname9, Firstname9
Lastname10, Firstname10

I have commented out this line

        '.Sort.SortFields.Add Key:=Range(.Cells(List2.Row, List2.Column + 1).Address), _
                              SortOn:=xlSortOnValues, Order:=xlAscending,

so you can compare the result with your expected result

Remove the comment (apostrophe) before you run it on the real lists.

Column J is for testing purposes and can be removed whenever you want.
I have left in Sheet1 (BU) so that you can copy and paste back in the original data at will.



Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.