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

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
>

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
> >

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
> > >

[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)