Hi all,
instead of reenventing the wheel I turn to this group with my case:
assume a single column consisting of 20 cells; now, 11 cells contain the
number 1, others are empty. What I'm looking for is an elegant way of
elaborating all possible combinations of these 11 pieces as how they can be
scattered throughout 20 empty cells. Mind you, this could be done by hand
(ahhhhh), but a few combinations could be ommitted simply by mistake, and to
accomplish this would be a real drudgery; that's why I'm looking for an
algorithm to have this done for me. Any ideas?
Mac.

Mac,

So you want to have every combination of 11 ones in 20 cells, well it's a
lot. Put the numbers 1 to 20 in A1 - A20

Right click the worksheet tab, view code and paste this in and run it.
Depending on your PC you may have time to make a coffee.

What it will do is produce a list of number the first of which are the
numbers 1 to 11 indicating that is your first combination a 1 in each of
those cells. The last combination are the numbers 10 to 20 which is your last
set of cells to put a number 1 in. Altogether there are 167960 combinations
so happy typing

Sub combinations56()
'Perm any 11 from 20
'Numbers 1 to 20 in Column A 1 - 20
col = 2
last = Cells(Rows.Count, "A").End(xlUp).Row
For a = 1 To last - 10
For b = a + 1 To last - 9
For c = b + 1 To last - 8
For d = c + 1 To last - 7
For e = d + 1 To last - 6
For f = e + 1 To last - 5
For g = f + 1 To last - 4
For h = g + 1 To last - 3
For i = h + 1 To last - 2
For j = i + 1 To last - 1
For k = j + 1 To last

Cells(L + 1, col) = Cells(a, 1) & ", " & Cells(b, 1) & ", "
& Cells(c, 1) & ", " & Cells(d, 1) _
& ", " & Cells(e, 1) & ", " & Cells(f, 1) & ", " & Cells(g,
1) & ", " & Cells(h, 1) & ", " & Cells(i, 1) & ", " & Cells(j, 1) & ", " &
Cells(k, 1)

If L < 65535 Then
L = L + 1
Else
L = 0
col = col + 1
End If
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
End Sub


Mike


"Mac" wrote:

Hi all,
instead of reenventing the wheel I turn to this group with my case:
assume a single column consisting of 20 cells; now, 11 cells contain the
number 1, others are empty. What I'm looking for is an elegant way of
elaborating all possible combinations of these 11 pieces as how they can be
scattered throughout 20 empty cells. Mind you, this could be done by hand
(ahhhhh), but a few combinations could be ommitted simply by mistake, and to
accomplish this would be a real drudgery; that's why I'm looking for an
algorithm to have this done for me. Any ideas?
Mac.