Match Columns While Making Space for Dupicates

eightbitannotations 

A macro:

Sub MatchCols()
    Dim r As Long
    Dim s As Long
    Application.ScreenUpdating = False
    r = 2
    Do
        If Range("M" & r) > Range("N" & r) Then
            Range("M" & r).Insert Shift:=xlShiftDown
        ElseIf Range("M" & r) < Range("N" & r) Then
            Range("N" & r).Insert Shift:=xlShiftDown
        End If
        r = r + 1
    Loop Until Range("M" & r).Value = ""
    Application.ScreenUpdating = True
End Sub

Please test on a copy of the data.

eightbitannotations 

The first problem my be that I use Excel 365 and only write solutions that use that version to the full.

The next problem is that MAP that should enable results to be stacked in the same pattern as the mapped array only works for functions that return scalars.

The Lambda function I used to stack a single value from list1 with corresponding values from list2 was

Stackλ
= LAMBDA(u, LAMBDA(v, IFERROR(HSTACK(v, FILTER(u, u=v, "")),"")));

which I used in the worksheet formula

= MAPλ(list1, Stackλ(list2))

The catch is that MAPλ is not the in-built Excel function, though it uses it.  The Lambda (helper) function I wrote can be downloaded to the AFE from A version of Excel MAP helper function that will return an array of arrays (github.com).

By now, those that follow my work will be getting somewhat bored, whilst others might be plain mystified.  Since writing the MAPλ function last week I have used it several times to provide solutions in which the thinking is already done and captured within the function.  The sooner the in-built function behaves in the same manner the better (the 'nested arrays are not supported' message is getting very boring!)

eightbitannotations 

With Power Query in attached file. Query1:

let
    Source = Table.NestedJoin(
        Table1, {"LIST1"},
        Table2, {"LIST2"},
        "MERGED_2",
        JoinKind.LeftOuter
    ),
    AddedList2 = Table.AddColumn(Source, "LIST2", each
        List.Combine( Table.ToColumns([MERGED_2]) ),
        List.Type
    ),
    RemovedMergedTable = Table.RemoveColumns(AddedList2, {"MERGED_2"}),
    ExpandedList2 = Table.AddColumn(RemovedMergedTable, "EXPANDED2", each
        if List.IsEmpty([LIST2]) then {null} else [LIST2],
        List.Type
    ),
    RemovedList2 = Table.RemoveColumns(ExpandedList2, {"LIST2"}),
    ExpandedList1 = Table.AddColumn(RemovedList2, "EXPANDED1", each
        if List.IsEmpty([EXPANDED2])
        then {[LIST1]}
        else {[LIST1]} & List.Repeat({null}, List.Count([EXPANDED2]) -1),
        List.Type
    ),
    RemovedList1 = Table.RemoveColumns(ExpandedList1, {"LIST1"}),
    ListsToTable = Table.FromColumns(
        List.Combine(
            {
                { List.Combine(RemovedList1[EXPANDED1]) },
                { List.Combine(RemovedList1[EXPANDED2]) }
            }
        ),
        {"LIST1", "LIST2"}
    )
in
    ListsToTable