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

[jemrisin]

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:

[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]Column A:
[/TD]
[TD]Column B:
[/TD]
[TD]Column C
[/TD]
[/TR]
[TR]
[TD]0001
[/TD]
[TD]0001
[/TD]
[TD]0001
[/TD]
[/TR]
[TR]
[TD]0002
[/TD]
[TD]0001
[/TD]
[TD]0002
[/TD]
[/TR]
[TR]
[TD]0003
[/TD]
[TD]0001
[/TD]
[TD]0003
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0002
[/TD]
[TD]0001
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0002
[/TD]
[TD]0002
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0002
[/TD]
[TD]0003
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0003
[/TD]
[TD]0001
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0003
[/TD]
[TD]0002
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0003
[/TD]
[TD]0003
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[oldbrewer]

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

 

[jemrisin]

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]

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]

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]

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]

Try:

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]

Try:

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