15 Variables, 3 Combinations

[futureancient]

Hello folks,

I have a tricky problem. I have Column A, Rows 1-15 with numbers in them, lets pretend they are numbers 1-15.

I need to get listed in separate columns and rows all possible combinations of 3 of these 15 numbers, and in the columns next to these the remaining 12 variables.

For example.. From column B to column P (15 columns), I need in Columns B,C and D, each row showing a different combination out of the 15 possible picks, and from column E to column P, the other 12 that weren't chosen in any order, though number order is probably advantageous.

This might look like..

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
1,2,4,3,5,6,7,8,9,10,11,12,13,14,15
1,2,5,3,4,6,7,8,9,10,11,12,13,14,15

And so on.... The numbers cannot be picked twice, so it is impossible to have a number on a row twice.

Can anyone help me out ?

 

[VoG]

Hi. Maybe the code here will help but you'll need to rearrange your data http://www.mrexcel.com/forum/showthread.php?t=532106

 

[futureancient]

Thats great thank you, it gets me the 3 pick combinations in column A separated with commas, I can work with that. Can you recommend a way to get the other 12 listed to the side ?

 

[VoG]

You would need to list them in a separate column. The results will appear on a new sheet. Copy those results and paste into the sheet with the results of the first run.

 

[Marcelo Branco]

Hi,

Try this in a test workbook

Sub Combine15by3()
    Dim i As Long, j As Long, k As Long, m As Long, lin As Long, col As Long
    Dim x As Variant
 
    x = Sheets("Plan1").Range("A1:A15")
 
    For i = 1 To 13
        For j = i + 1 To 14
            For k = j + 1 To 15
                lin = lin + 1
                Range("B" & lin) = x(i, 1)
                Range("B" & lin).Offset(0, 1) = x(j, 1)
                Range("B" & lin).Offset(0, 2) = x(k, 1)
                col = 2
                For m = 1 To 15
                    If m <> i And m <> j And m <> k Then
                        col = col + 1
                        Range("B" & lin).Offset(0, col) = x(m, 1)
                    End If
                Next m
            Next k
        Next j
    Next i
 
End Sub

HTH

M.

 

[Marcelo Branco]

Forgot to say
Change the Sheet-name accordingly from Plan1 to your sheet-name

M.

 

[futureancient]

Update on this..

It's still not quite working correctly. I get all the 3 combinations, so long as the next pick is higher in number than the last...

For example,

1,4,5...
1,4,6...
1,4,7...
1,4,8...

But before this there doesn't exist the combinations..

1,4,2...
1,4,3...

Higher up the list of combinations, there are no combinations that begin with 14, or 15, because they are used in prior combinations in the sets of 3. For example, I'd expect to see..

14,1,2
14,1,3
14,1,4
14,1,5

and so on. But the list stops at 13,14,15.

It took me a while to notice this, I should check more thoroughly. Marcelo or VoG, or anyone, can you help me on this ?

 

[Marcelo Branco]

Update on this..

It's still not quite working correctly. I get all the 3 combinations, so long as the next pick is higher in number than the last...

For example,

1,4,5...
1,4,6...
1,4,7...
1,4,8...

But before this there doesn't exist the combinations..

1,4,2...
1,4,3...

Higher up the list of combinations, there are no combinations that begin with 14, or 15, because they are used in prior combinations in the sets of 3. For example, I'd expect to see..

14,1,2
14,1,3
14,1,4
14,1,5

and so on. But the list stops at 13,14,15.

It took me a while to notice this, I should check more thoroughly. Marcelo or VoG, or anyone, can you help me on this ?

Hi,

If we are talking about combinations the order is not relevant.
Instead of
1,4,2
1,4,3
14,1,2
14,1,3
14,1,4
14,1,5

the macro lists
1,2,4
1,3,4
1,2,14
1,3,14
1,4,14
1,5,14

If you want for the first 3 numbers, as example:
1,2,3
1,3,2
2,1,3
2,3,1
3,1,2
3,2,1

you need Permutations, not Combinations

M.