formula setup for number combinations

[The Deacon]

I am having a problem setting up a function in Excel (not sure of the
appropriate function to use). The fundtion I use can be used for numbers or
text (no combinations of numbers and text)

I have 6 groups of numbers with each group labeled.

GRP A GRP B

G1 1 2
G2 3 4
G3 5 6
G4 7 8
G5 9 10
G6 11 12

I am trying to pull one number from each of the 6 groups to produce a number
combination of 6. The combinations must stay in number order in the lowest
to the highest. I quess to make it plain I have 12 numbers and i want to
create a combination of 6 numbers making sure I only use one number from each
group. See example below.


COMBN1 COMBN2 COMBN3
G1 1 2 1
G2 3 3 4
G3 5 5 5
G4 7 7 7
G5 9 9 9
G6 11 11 11

i hope what I have displayed is what I am trying to relate in my message and
things are to confusing.

The Deacon

 

[Herbert Seidenberg]

Excel 2007
Tables, Combinations,
Dec2Bin, Structured References:
http://www.mediafire.com/file/ldm2mtjjhnd/04_04_09.xlsx

 

[Bernd P]

Hello,

Maybe this is not what you were looking for, but this VBA approach
seems fairly straight forward to me:
Sub test()
Dim g1 As Long, g2 As Long, g3 As Long
Dim g4 As Long, g5 As Long, g6 As Long
Dim i As Long
i = 1
For g1 = 1 To 2
For g2 = 3 To 4
For g3 = 5 To 6
For g4 = 7 To 8
For g5 = 9 To 10
For g6 = 11 To 12
Cells(i, 1) = g1
Cells(i, 2) = g2
Cells(i, 3) = g3
Cells(i, 4) = g4
Cells(i, 5) = g5
Cells(i, 6) = g6
i = i + 1
Next g6
Next g5
Next g4
Next g3
Next g2
Next g1
End Sub

Regards,
Bernd

 

[Gary''s Student]

Run this simple macro:

Sub deacon()
Dim i As Integer, j As Integer, k As Integer, l As Integer, m As Integer, n
As Integer
Dim roww As Long
roww = 1
For i = 1 To 2
For j = 1 To 2
For k = 1 To 2
For l = 1 To 2
For m = 1 To 2
For n = 1 To 2
Cells(roww, 1) = i
Cells(roww, 2) = j + 2
Cells(roww, 3) = k + 4
Cells(roww, 4) = l + 6
Cells(roww, 5) = m + 8
Cells(roww, 6) = n + 10
roww = roww + 1
Next
Next
Next
Next
Next
Next
End Sub

will produce these 64 combinations:

1 3 5 7 9 11
1 3 5 7 9 12
1 3 5 7 10 11
1 3 5 7 10 12
1 3 5 8 9 11
1 3 5 8 9 12
1 3 5 8 10 11
1 3 5 8 10 12
1 3 6 7 9 11
1 3 6 7 9 12
1 3 6 7 10 11
1 3 6 7 10 12
1 3 6 8 9 11
1 3 6 8 9 12
1 3 6 8 10 11
1 3 6 8 10 12
1 4 5 7 9 11
1 4 5 7 9 12
1 4 5 7 10 11
1 4 5 7 10 12
1 4 5 8 9 11
1 4 5 8 9 12
1 4 5 8 10 11
1 4 5 8 10 12
1 4 6 7 9 11
1 4 6 7 9 12
1 4 6 7 10 11
1 4 6 7 10 12
1 4 6 8 9 11
1 4 6 8 9 12
1 4 6 8 10 11
1 4 6 8 10 12
2 3 5 7 9 11
2 3 5 7 9 12
2 3 5 7 10 11
2 3 5 7 10 12
2 3 5 8 9 11
2 3 5 8 9 12
2 3 5 8 10 11
2 3 5 8 10 12
2 3 6 7 9 11
2 3 6 7 9 12
2 3 6 7 10 11
2 3 6 7 10 12
2 3 6 8 9 11
2 3 6 8 9 12
2 3 6 8 10 11
2 3 6 8 10 12
2 4 5 7 9 11
2 4 5 7 9 12
2 4 5 7 10 11
2 4 5 7 10 12
2 4 5 8 9 11
2 4 5 8 9 12
2 4 5 8 10 11
2 4 5 8 10 12
2 4 6 7 9 11
2 4 6 7 9 12
2 4 6 7 10 11
2 4 6 7 10 12
2 4 6 8 9 11
2 4 6 8 9 12
2 4 6 8 10 11
2 4 6 8 10 12

 

[joel]

I set up an arry with 12 items. You can change these items to any
combination of number and or letters or strings. Try code as is before
changing.

Sub Combinations()

Dim Combo()

Data = Array("a", "b", "c", "d", "e", "f", _
"g", "h", "i", "j", "k", "l")
DataLen = UBound(Data) + 1

Do
Size = Val(InputBox("Enter Size from 1 to " & DataLen))

Loop While Size <= 0 And Size > DataLen

ReDim Combo(Size)
Level = 1
RowCount = 1
activesheet.cells.clearcontents
Call Recursive(Data, Combo(), Level, Size, RowCount)

End Sub
Sub Recursive(Data, Combo, Level, Size, RowCount)

DataLen = UBound(Data) + 1
'make combination
For Count = (Combo(Level - 1) + 1) To _
DataLen - (Size - Level)

Combo(Level) = Count
If Level = Size Then
For ColCount = 1 To Size
Cells(RowCount, ColCount) = _
Data(Combo(ColCount) - 1)
Next ColCount
RowCount = RowCount + 1
Else
Call Recursive(Data, Combo, Level + 1, Size, RowCount)
End If
Next Count
End Sub

 

[The Deacon]

Hi Joel,

While I am trying to understand the programming you have submitted, is there
a specific function or functions that will create the answer on an excel
spreadsheet?

The Deacon

 

[The Deacon]

Hi Gary's Student - I'm looking at the programming you created but I can't
fiqure out what functions you are actually usong to get the outcome. Can you
provide me with the list or lists of functions I should use on an Excel
spreadsheet to calculate the outcome. Thanks.

The Deacon

 

[joel]

The answer is put on the worksheet. There isn't any worksheet formula that
gives the results you want. the code performs a the following count
sequence. where the number represents the index to the array Data. the
number of results is the formula

12!/(6! * 6!) = (12 * 11 * 10 * 9 * 8 * 7)/(6 * 5 * 4 * 3 * 2 * 1)

cancelling terms on top and bottom (12 = 2 * 6), (10 = 5 * 2), (8 = 4 * 2)
= 1 * 11 * 2 * 3 * 2 * 7 = 22 * 42 = 924

1 2 3 4 5 6
1 2 3 4 5 7
1 2 3 4 5 8
up to
1 2 3 4 5 12
1 2 3 4 6 7
1 2 3 4 6 8
up to
1 2 3 4 6 12
up to
1 2 3 4 11 12
up to
1 2 3 10 11 12
up to
1 2 9 10 11 12
up to
1 8 9 10 11 12
up to
7 8 9 10 11 12

 

[The Deacon]

Thanks Joel

The Deacon

The answer is put on the worksheet. There isn't any worksheet formula that
gives the results you want. the code performs a the following count
sequence. where the number represents the index to the array Data. the
number of results is the formula

12!/(6! * 6!) = (12 * 11 * 10 * 9 * 8 * 7)/(6 * 5 * 4 * 3 * 2 * 1)

cancelling terms on top and bottom (12 = 2 * 6), (10 = 5 * 2), (8 = 4 * 2)
= 1 * 11 * 2 * 3 * 2 * 7 = 22 * 42 = 924

1 2 3 4 5 6
1 2 3 4 5 7
1 2 3 4 5 8
up to
1 2 3 4 5 12
1 2 3 4 6 7
1 2 3 4 6 8
up to
1 2 3 4 6 12
up to
1 2 3 4 11 12
up to
1 2 3 10 11 12
up to
1 2 9 10 11 12
up to
1 8 9 10 11 12
up to
7 8 9 10 11 12

 

[joel]

I was wrong. It took me 2 hours tonight but I found a group of formulas that
will give you the same results as the macro

this is the sequence we want in column A - F

1 2 3 4 5 6
1 2 3 4 5 7
1 2 3 4 5 8
up to
1 2 3 4 5 12
1 2 3 4 6 7
1 2 3 4 6 8
up to
1 2 3 4 6 12
up to
1 2 3 4 11 12
up to
1 2 3 10 11 12
up to
1 2 9 10 11 12
up to
1 8 9 10 11 12
up to
7 8 9 10 11 12

First put in Row 1 A - F

A1 = 1, B1 = 2, C1 = 3, D1 = 4, E1 = 5, E6 = 6

Now E6 will go from 6 to 12 and when it reaches 12 will go to the value of
the previous value in column E

So the formula in F2 is

=IF(F1=12,E2+1,F1+1)

Now Column E is a similar formula except column E only reaches 11. You need
the 2nd IF to detect a ripple from the column(s) to the left. It is like
counting from 99 to 100.

So the formula in E2 is

=IF(F1=12,IF(E1=11,D2+1,E1+1),E1)

So the formula in D2 is

=IF(AND(E1=11,F1=12),IF(D1=10,C2+1,D1+1),D1)

So the formula in C2 is

=IF(AND(D1=10,E1=11,F1=12),IF(C1=9,B2+1,C1+1),C1)

So the formula in B2 is

=IF(AND(C1=9,D1=10,E1=11,F1=12),IF(B1=8,A2+1,B1+1),B1)

So the formula in A2 is

=IF(AND(B1=8,C1=9,D1=10,E1=11,F1=12),A1+1,A1)


Now copy the formulas in Row to up to Row 924. You can use the numbers
generated in columns A - F to create the letter you want.

So the formula in G1 =CHAR(CODE("a")+A1-1)

Copy the formual to G1:L924

Column G to L is the results you are looking for.

 

[Dana DeLouis]

I am trying to pull one number from each of the 6 groups

For i = 1 To 2
For j = 1 To 2
For k = 1 To 2
For l = 1 To 2
For m = 1 To 2
For n = 1 To 2

Hi Gary. As a side note, you may be interested in the following.
In Math programs, (and some fancy database programming) this is called
"Tuples", vs Combinations.
It is done via a recursive call taking two inputs at a time.
One starts with the first two array of values...

Tuples({{1, 2}, {3, 4}})
{{1, 3}, {1, 4}, {2, 3}, {2, 4}}

This output is used along with the next array of {5,6} to produce the
next output. Keep looping, and the output is as desired...

{1, 3, 5, 7, 9, 11}
{1, 3, 5, 7, 9, 12}
....etc
{2, 4, 6, 8, 10, 11}
{2, 4, 6, 8, 10, 12}

The advantage is that size of the code can be fixed. (ie just two
inputs). Again, thought you might find the concept interesting. :>)
Dana DeLouis

 

[joel]

Dana: Did you see my recursive program that I posted on 7/5. I made it very
flexible that all yo have to change is the number of items it the Array
Data() to get for different data strings and the macro has an input box for
the lenghts of the strings.

 

[Dana DeLouis]

Hi Joel. Yes, excellent code! Thanks. I may be wrong, but the op said
that he wanted 1 item from each of the 6 groups.
(...I am trying to pull one number from each of the 6 groups...)

Your Subsets of size 6...
1 2 3 4 5 6
1 2 3 4 5 7
1 2 3 4 5 8

The way I read it, these would not be valid because both 1&2 come from
the same group. (as is 3&4, etc)
I may be wrong, but I believe there are 64 possible outcomes, vs your
=Combin(12,6) -> 924 possible outcomes.

Again, I may be wrong.
Dana DeLouis

 

[joel]

On the original 4/4 posting the request was confusing put it said the
following:

I am trying to pull one number from each of the 6 groups to produce a number
combination of 6. The combinations must stay in number order in the lowest
to the highest. I quess to make it plain I have 12 numbers and i want to
create a combination of 6 numbers making sure I only use one number from each
group. See example below.