# List all possible combinations of a single range in two seperate columns

#### jemrisin

##### New Member
I have a single rangle of numbers in Column A, and in Columns B and C and want to list all possible combinations of these numbers. If there is a possible solution without using a Macro I would perfer that, but if not I'll take what I can get.

Example Desired Output:

 Column A: Column B: Column C 0001 0001 0001 0002 0001 0002 0003 0001 0003 0002 0001 0002 0002 0002 0003 0003 0001 0003 0002 0003 0003

<TBODY>
</TBODY>

#### oldbrewer

##### Board Regular
1-2 is the same as 2-1 if you are talking combinations. did you mean permutations?

#### jemrisin

##### New Member
1-2 is the same as 2-1 if you are talking combinations. did you mean permutations?

Yes, I meant permutations. In this example 1-2 is NOT the same as 2-1, these would need to be listed seperately (see example). Sorry for the confusion, I was not aware of the difference in terminology.

#### oldbrewer

##### Board Regular
in B1 put =A1 in C1 put = A1 ( gives 0001 0001 )

in B2 put =A1 in C2 put = A2 ( gives 0001 0002 )

put all permutations in but look out you will get 0001 0001 twice ditto 0002 0003

you will need a macro to remove these so why not have a macro to do it right in the first place?

#### nuked

##### Well-known Member
Code:
``````Option Explicit
Sub Perms()
Dim lListLength As Long
Dim lCount As Long
Dim x As Long, y As Long
Dim wks As Worksheet
Dim cel As Range
Set wks = Sheets("Sheet1")
Set cel = wks.Range("A1")
lListLength = wks.Range("A" & wks.Rows.Count).End(xlUp).Row - 1
For x = 0 To lListLength
For y = 0 To lListLength

cel.Offset(lCount, 1) = cel.Offset(x, 0)
cel.Offset(lCount, 2) = cel.Offset(y, 0)

lCount = lCount + 1

Next y
Next x
End Sub``````

#### jemrisin

##### New Member
Code:
``````Option Explicit
Sub Perms()
Dim lListLength As Long
Dim lCount As Long
Dim x As Long, y As Long
Dim wks As Worksheet
Dim cel As Range
Set wks = Sheets("Sheet1")
Set cel = wks.Range("A1")
lListLength = wks.Range("A" & wks.Rows.Count).End(xlUp).Row - 1
For x = 0 To lListLength
For y = 0 To lListLength

cel.Offset(lCount, 1) = cel.Offset(x, 0)
cel.Offset(lCount, 2) = cel.Offset(y, 0)

lCount = lCount + 1

Next y
Next x
End Sub``````
Unless I misunderstood, the first option will not work because there are 376 numbers in the list I am using meaning there will should be 141,376 rows of permutations created. I tried the macro above and recieved the error "Subscript out of range." I'm guessing this is a user error considering I have limited experience with Macros. Can you let me know what I did wrong:

-Ensured my data is in rows A1:A376
-hit Alt+F11
-Selected the appropriate sheet
-Pasted the code
-Hit the "Run" button

#### nuked

##### Well-known Member
Try:

Code:
``````Option Explicit
Sub Perms()
Dim lListLength As Long
Dim lCount As Long
Dim x As Long, y As Long
Dim wks As Worksheet
Dim cel As Range
Set wks = Activesheet
Set cel = wks.Range("A1")
lListLength = wks.Range("A" & wks.Rows.Count).End(xlUp).Row - 1
For x = 0 To lListLength
For y = 0 To lListLength

cel.Offset(lCount, 1) = cel.Offset(x, 0)
cel.Offset(lCount, 2) = cel.Offset(y, 0)

lCount = lCount + 1

Next y
Next x
End Sub``````

#### jemrisin

##### New Member
Try:

Code:
``````Option Explicit
Sub Perms()
Dim lListLength As Long
Dim lCount As Long
Dim x As Long, y As Long
Dim wks As Worksheet
Dim cel As Range
Set wks = Activesheet
Set cel = wks.Range("A1")
lListLength = wks.Range("A" & wks.Rows.Count).End(xlUp).Row - 1
For x = 0 To lListLength
For y = 0 To lListLength

cel.Offset(lCount, 1) = cel.Offset(x, 0)
cel.Offset(lCount, 2) = cel.Offset(y, 0)

lCount = lCount + 1

Next y
Next x
End Sub``````
Genius! THANK YOU! I really need to learn VBA...