Merge two columns with non-duplicates and sort

Hi Marc. One thing I would like to add to your script.
In range C:E I have formulas with absolute references. So, would it be possible to copy them as values? Right now, when I use your macro, it copies the name corectly but adds only "0" values.

Try this

Sub MergeListsBEandLO()
    Dim LastRowBE As Long, LastRowLO As Long
    
    LastRowBE = Range("B" & Rows.Count).End(xlUp).Row
    LastRowLO = Range("L" & Rows.Count).End(xlUp).Row
    
    Range("B1:E" & LastRowBE).Copy
    Range("L" & LastRowLO + 1).PasteSpecial Paste:=xlPasteValues, _
                            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                            
    LastRowLO = Range("L" & Rows.Count).End(xlUp).Row
    
    Application.CutCopyMode = False
    ActiveSheet.Range("$L$1:$O$" & LastRowLO).RemoveDuplicates Columns:=1, Header:=xlNo
    
    LastRowLO = Range("L" & Rows.Count).End(xlUp).Row
    
    Worksheets("Sheet1").Sort.SortFields.Clear
    
    Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("L1"), _
                              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("L1:O" & LastRowLO)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Paste all of the above in place of the original macro

Should do the job

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.

Can someone help me out with what i wrote in post #5?

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.

Thanks Marc. I waited to make sure I don't encouter any problems with the code. It works great as usual. Thanks for your help (all of it) and I'm sorry if I rushed you (i thought you left my thread for good). I will add some reputation to you for your help.
Thanks you so much and I guess I'll see you around. SOLVED!!