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

jemrisin

New Member
Joined
Apr 25, 2013
Messages
8
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
Joined
Apr 11, 2010
Messages
10,985
1-2 is the same as 2-1 if you are talking combinations. did you mean permutations?
 

jemrisin

New Member
Joined
Apr 25, 2013
Messages
8
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
Joined
Apr 11, 2010
Messages
10,985
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
Joined
Mar 20, 2013
Messages
883
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
Joined
Apr 25, 2013
Messages
8
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
Joined
Mar 20, 2013
Messages
883
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
Joined
Apr 25, 2013
Messages
8
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...
 

Forum statistics

Threads
1,082,478
Messages
5,365,780
Members
400,849
Latest member
candy2052

Some videos you may like

This Week's Hot Topics

Top