# Keep a total of distribution totals.

#### S.H.A.D.O.

##### Well-known Member
Good evening,

I would like to cycle through all C(49,6) combinations and keep a count of the distribution for each combination for each of the different Sets.
By this I mean the following, there are 8 sets of numbers:-

Set 1 = 01,09,17,25,33
Set 2 = 02,10,18,26,34
Set 3 = 03,11,19,27,35
Set 4 = 04,12.20.28,36
Set 5 = 05,13,21,29,37
Set 6 = 06,14,22,30,38
Set 7 = 07,15,23,31,39
Set 8 = 08,16,24,32

So for the first combination 01,02,03,04,05,06 the distribution would be 111111 because the 6 numbers are from 6 different sets.

So for combination 09,17,28,31,32,38 the distribution would be 211110 because there are 2 numbers from one 1 Set and the other 4 numbers from 4 different sets.

So for combination 04,12,20,21,29,38 the distribution would be 321000 because there are 3 numbers from one Set, 2 numbers from another Set and 1 number from another different Set.

At the end I would like the GRAND total for ALL the posiible Sets please.
The Sets are:-

111111 = x combinations
211110 = x combinations
221100 = x combinations
222000 = x combinations
311100 = x combinations
321000 = x combinations
330000 = x combinations
411000 = x combinations
420000 = x combinations
510000 = x combinations
600000 = x combinations
Grand Total = x combinations

Here is the main code I have got so far:-

Code:
``````Option Explicit
Option Base 1

Private Map(1 To 11) As Long

Const MinA As Integer = 1
Const MaxF As Integer = 49

Sub Distribution()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Dim n As Long
Dim nDist(11) As Double
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
Map(1) = 111111
Map(2) = 211110
Map(3) = 221100
Map(4) = 222000
Map(5) = 311100
Map(6) = 321000
Map(7) = 330000
Map(8) = 411000
Map(9) = 420000
Map(10) = 510000
Map(11) = 600000
Columns("A:B").ClearContents
Cells(1, 1).Select
For A = MinA To MaxF - 5
For B = A + 1 To MaxF - 4
For C = B + 1 To MaxF - 3
For D = C + 1 To MaxF - 2
For E = D + 1 To MaxF - 1
For F = E + 1 To MaxF

'   Calculation to go here.

Next F
Next E
Next D
Next C
Next B
Next A
For n = 1 To UBound(nDist)
ActiveCell.Offset(0, 0).Value = Map(n)
ActiveCell.Offset(0, 1).Value = nDist(n)
ActiveCell.Offset(1, 0).Select
Next n
ActiveCell.Offset(0, 1).FormulaR1C1 = "=Sum(R1C2:R[-1]C)"
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub``````

I have tried using Case statements etc but can't get even close.

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Replies
2
Views
325
Replies
1
Views
11K
Replies
3
Views
183
Replies
2
Views
2K
Replies
0
Views
323

1,196,425
Messages
6,015,171
Members
441,882
Latest member
LostinExcelHelp

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back