Pair cells in Excel

Question

I have two columns in Excel with different values:

A 1
B 2
C 3

Now, I would need to pair each cell of first column with each cell of second column. So it would look like this:

A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3

Do you know how can I do this please?

Thank you heaps

Answer 1

I modify Gary's answer with array. Not tested due to my Mac without Excel.

Sub MakeCombinations()
Dim Ary_a As Variant, Ary_b As Variant, Ary as Variant
Dim i As Long, j As Long

Ary_a = range(Cells(rows.count, 1).End(xlUp).Row, 1).value
Ary_b = range(Cells(rows.count, 2).End(xlUp).Row, 2).value

For i = lbound(ary_a) To ubound(ary_a)
    For j = lbound(ary_b) To ubound(ary_b)
        if not isarray(ary) then 
            redim ary(1, 0)
        else 
            redim preserve ary(1, ubound(ary, 2)+1)
        end if

        ary(0, ubound(ary, 2)) = ary_a(i)
        ary(1, ubound(ary, 2)) = ary_b(j)
    Next j
Next i

cells(1, 4).resize(ubound(ary, 2)+1, ubound(ary, 1)+1).value = application.transpose(ary)
End Sub

Answer 2

With data in columns A and B try this short macro:

Sub MakeCombinations()
    Dim Na As Long, Nb As Long
    Dim i As Long, j As Long, K As Long
    Dim rc As Long

    K = 1
    rc = Rows.Count
    Na = Cells(rc, 1).End(xlUp).Row
    Nb = Cells(rc, 2).End(xlUp).Row

    For i = 1 To Na
        For j = 1 To Nb
            Cells(K, 3) = Cells(i, 1)
            Cells(K, 4) = Cells(j, 2)
            K = K + 1
        Next j
    Next i
End Sub

EDIT#1:

To do this without VBA, in C1 enter:

=INDEX(A:A,ROUNDUP(ROW()/COUNTA(B:B),0),1)

and copy down and in D1 enter:

=INDEX(B:B,MOD(ROW()-1,COUNTA(B:B))+1,1)

and copy down: