How can excel generate every unique pair from a set of numbers?

[mistermat]

If I have a set of unique numbers, for example:

1
2
3
4

how can i use excel to generate every unique pair automatically in two
separate columns, for example:

1 2
1 3
1 4
2 1
2 3
2 4
3 1
3 2
3 4
4 1
4 2
4 3

 

[Mike H]

Hi,

This assumes you numbers are in column A and it will output to columns B & C

Right click your sheet tab, view code and past this in and run it

Sub stantial()
Range("B1:C" & Cells(Rows.Count, "B").End(xlUp).Row).ClearContents
myrow = 1
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = 1 To lastrow
For y = x + 1 To lastrow
Cells(myrow, 2).Value = Cells(x, 1).Value
Cells(myrow, 3).Value = Cells(y, 1).Value
myrow = myrow + 1
Cells(myrow, 2).Value = Cells(y, 1).Value
Cells(myrow, 3).Value = Cells(x, 1).Value
myrow = myrow + 1
Next
Next
Range("B1:C" & Cells(Rows.Count, "B").End(xlUp).Row).Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending
End Sub

Mike

 

[mistermat]

Mike, this is perfect. Thank you so much, I really appreciate your help!

 

[Mike H]

Glad I could help

Mike, this is perfect. Thank you so much, I really appreciate your help!

 

[mikerickson]

If your numbers are in A1 and downward, putting this formula in B1 and dragging down will work.
=INDEX(A:A,MOD(INT((ROW()-1)/COUNT(A:A)),COUNT(A:A))+1,1) &","& INDEX(A:A,MOD(ROW()-1,COUNTA(A:A))+1,1)