You are here:

Excel/Help in Combinations

Advertisement


Question
QUESTION: Hi,

I need to make a excel plan with some combinations.
I have used a códe created by Tom Ogilvy but he is on vacation and i have some hurry to solve it.

see if you can help me.

I need to make all the possibles combinations with numbers from 1 to 10 with 6 combinations.

i have this code and its work very nice but i need that they repeat, like this:

1 2 3 4 5 6
1 1 1 1 1 6
1 2 2 3 4 4

all the possibles combinations, i'm not good in english but i'm trying to do the best explanation i can.

The Code:

Option Explicit

'C(n, p) = n! / ((n-p)! * p!)
'lPermutações a ser definido, seria o 'p' da fórmula acima
Const lPermutações As Long = 3

Dim r As Long

Dim v(1 To 5)

Sub Teste()
   Dim lElementos As Long
   
   'Popula vetor de elementos
   v(1) = "a"
   v(2) = "b"
   v(3) = "c"
   v(4) = "d"
   v(5) = "e"
   
   'C(n, p) = n! / ((n-p)! * p!)
   'lElementos seria o 'n' da fórmula acima
   lElementos = UBound(v) - LBound(v) + 1
   
   'Contador de linhas para uso no Excel:
   r = 0
   
   'Limpa Planilha ativa
   Cells.Delete
   
   'Inicia recursão:
   Combinação lElementos, lPermutações, 1
End Sub

Sub Combinação(n As Long, p As Long, k As Long, Optional s As String)
   If p > n - k + 1 Then Exit Sub
   If p = 0 Then
       'Para visualizar o resultado de uma combinação no Excel:
       r = r + 1
       Cells(r, "A").Resize(1, lPermutações) = Split(s, "|")
       'Se quiser visualizar o resultado na Janela de Verificação imediata, use:
       Debug.Print s
       Exit Sub
   End If
   'Recorre novamente:
   Combinação n, p - 1, k + 1, s & v(k) & "|"
   'Recorre novamente a partir do elemento anterior:
   Combinação n, p, k + 1, s
End Sub

ANSWER: If I've understood the question, every number could be in any location, so it would go from 111111 to 101010101010 as the possible choices - if so, there are one million combinations - these can be generated with this code

Sub aidancode()
Dim a, b, c, d, e, f As Long
Dim outvar As Long
outvar = 2
For a = 1 To 10
   For b = 1 To 10
       For c = 1 To 10
         For d = 1 To 10
         For e = 1 To 10
         For f = 1 To 10
         Cells(outvar, 1).Value = a
         Cells(outvar, 2).Value = b
         Cells(outvar, 3).Value = c
         Cells(outvar, 4).Value = d
         Cells(outvar, 5).Value = e
         Cells(outvar, 6).Value = f
         outvar = outvar + 1
         Next
         Next
         Next
       Next
   Next
Next
End Sub


---------- FOLLOW-UP ----------

QUESTION: Thanck you very much, it work!!!

But i have just more one question, i saw that in the macro you put "1 To 10" but how can i change this values for exemple:11.57, 11.78, 11.84, etc.

Not from X value to Y value, but 10 values that i want to put in the table.

I can chance in the macro "1 To 10" for "1 or 2 or 3... etc??"

Answer
If you want any 10 consecutive numbers you can simply amend the for..next loop.  If as is more likely the numbers are not consecutive then this modified version would work for you.


Sub aidancode()
Dim a, b, c, d, e, f As Long
Dim outvar As Long
Dim Choices(10)
Choices(1) = 1
Choices(2) = 2
Choices(3) = 3
Choices(4) = 4
Choices(5) = 5
Choices(6) = 6
Choices(7) = 7
Choices(8) = 8
Choices(9) = 9
Choices(10) = 10
'change the above lines to have whatever 10 choices you want
outvar = 2

For a = 1 To 10
  For b = 1 To 10
      For c = 1 To 10
        For d = 1 To 10
         For e = 1 To 10
         For f = 1 To 10
         Cells(outvar, 1).Value = Choices(a)
         Cells(outvar, 2).Value = Choices(b)
         Cells(outvar, 3).Value = Choices(c)
         Cells(outvar, 4).Value = Choices(d)
         Cells(outvar, 5).Value = Choices(e)
         Cells(outvar, 6).Value = Choices(f)
         outvar = outvar + 1
         Next
         Next
        Next
      Next
  Next
Next
End Sub



Just change the ten choices(x) lines to have the values you want to return.  Sorry for the slight delay in replying - I'm in the UK and was asleep when you asked the question!
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.