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

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

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

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" wrote:

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

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

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

"Mike H" wrote:

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" wrote:

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

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

Glad I could help

"mistermat" wrote:

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

"Mike H" wrote:

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" wrote:

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