Creating a combination list with using 2 lists

[neutral5]

Screenshot_1.jpg

How can I create "Final" list with using "List 1" and "List 2"? I created "Final" list manually, just to show what I want.

[alansidman]

Here is a VBA solution

Option Explicit

Sub NewList()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim lrA As Long, lrB As Long, lr2 As Long
    lrA = s1.Range("A" & Rows.Count).End(xlUp).Row
    lrB = s1.Range("B" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    Dim i As Long, j As Long
    For i = 2 To lrA
        For j = 2 To lrB
            lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row + 1
            s2.Range("A" & lr2) = s1.Range("A" & i) & s1.Range("B" & j)
        Next j
    Next i
    Application.ScreenUpdating = True
    MsgBox "Process Completed"
End Sub

[Bo_Ry]

Please try at D2

=IF(ROWS(D$2:D2)>COUNTA(A$2:A$9)*COUNTA(B$2:B$9),"",INDEX($A$2:$A$9,ROUNDUP(ROWS(D$2:D2)/COUNTA($B$2:$B$9),))&INDEX($B$2:$B$9,MOD(ROWS(D$2:D2)-1,COUNTA($B$2:$B$9))+1))