Calc Combinations Automaization

[Nykyta]

I have 12 events and possible hit of event result. Hits are to be groped in 3 together (combination). For example, my hits are: 1, 3, 6, 7 & 8. I need to get all combinations of 3 hits ie.: 1-3-6, 1-3-7, 1-3-8, 1-6-7, 1-6-8, 1-7-8, 3-6-7. 3-6-8, 3-7-8, 6-7-8. I have no idea how to automate that process. Any help, any idea is appreciated (VBA too). Sample Workbook posted: http://laban.rs/files/Book1.xlsx

Thanks in advance.

 

[michaelsmith559]

Here is a code that I found online by Myrna Larson. I am using excel 2013. If you are using excel 2003 there is a line in the code that has to be changed. I can't remember at the moment which line it is. Anyhow, set up your sheet like this (I put a couple of notes on the sheet for you in column B, you do not need to put these on your sheet):

Sheet 1 setup:

Excel 2012
	A	B	C
1	C	C for combinations or P for Permutations
2	3	Number to take at a time
3	1	Start values here in ascending order
4	2	Select cell A1 before running macro
5	3
6	4
7	5
8	6
9	7
10	8
11
Sheet1

Results after macro:

Excel 2012
	A
1	1, 2, 3
2	1, 2, 4
3	1, 2, 5
4	1, 2, 6
5	1, 2, 7
6	1, 2, 8
7	1, 3, 4
8	1, 3, 5
9	1, 3, 6
10	1, 3, 7
11	1, 3, 8
12	1, 4, 5
13	1, 4, 6
14	1, 4, 7
15	1, 4, 8
16	1, 5, 6
17	1, 5, 7
18	1, 5, 8
19	1, 6, 7
20	1, 6, 8
21	1, 7, 8
22	2, 3, 4
23	2, 3, 5
24	2, 3, 6
25	2, 3, 7
26	2, 3, 8
27	2, 4, 5
28	2, 4, 6
29	2, 4, 7
30	2, 4, 8
31	2, 5, 6
32	2, 5, 7
33	2, 5, 8
34	2, 6, 7
35	2, 6, 8
36	2, 7, 8
37	3, 4, 5
38	3, 4, 6
39	3, 4, 7
40	3, 4, 8
41	3, 5, 6
42	3, 5, 7
43	3, 5, 8
44	3, 6, 7
45	3, 6, 8
46	3, 7, 8
47	4, 5, 6
48	4, 5, 7
49	4, 5, 8
50	4, 6, 7
51	4, 6, 8
52	4, 7, 8
53	5, 6, 7
54	5, 6, 8
55	5, 7, 8
56	6, 7, 8
Sheet2

Macro:

Option Explicit
Dim vAllItems As Variant
Dim Buffer() As String
Dim BufferPtr As Long
Dim Results As Worksheet
'
'  Posted by Myrna Larson
'  July 25, 2000
'  Microsoft.Public.Excel.Misc
'  Subject:  Combin
'
'
'Since you asked, here it is. It is generic, i.e. it isn't written specifically
'for a given population and set size, as yours it. It will do permutations or
'combinations. It uses a recursive routine to generate the subsets, one routine
'for combinations, a different one for permutations.
'To use it, you put the letter C or P (for combinations or permutations) in a
'cell. The cell below that contains the number of items in a subset. The Cells
'below are a list of the items that make up the population. They could be
'numbers, letters and symbols, or words, etc.
'You select the top cell, or the entire range and run the sub. The subsets are
'written to a new sheet in the workbook.
'
'
Sub ListPermutations()
  Dim Rng As Range
  Dim PopSize As Integer
  Dim SetSize As Integer
  Dim Which As String
  Dim N As Double
  Const BufferSize As Long = 4096
  Set Rng = Selection.Columns(1).Cells
  If Rng.Cells.Count = 1 Then
    Set Rng = Range(Rng, Rng.End(xlDown))
  End If
  PopSize = Rng.Cells.Count - 2
  If PopSize < 2 Then GoTo DataError
  SetSize = Rng.Cells(2).Value
  If SetSize > PopSize Then GoTo DataError
  Which = UCase$(Rng.Cells(1).Value)
  Select Case Which
  Case "C"
    N = Application.WorksheetFunction.Combin(PopSize, SetSize)
  Case "P"
    N = Application.WorksheetFunction.Permut(PopSize, SetSize)
  Case Else
    GoTo DataError
  End Select
  If N > Cells.CountLarge Then GoTo DataError
  Application.ScreenUpdating = False
  Set Results = Worksheets.Add
  vAllItems = Rng.Offset(2, 0).Resize(PopSize).Value
  ReDim Buffer(1 To BufferSize) As String
  BufferPtr = 0
  If Which = "C" Then
    AddCombination PopSize, SetSize
  Else
    AddPermutation PopSize, SetSize
  End If
  vAllItems = 0
  Application.ScreenUpdating = True
  Exit Sub
DataError:
  If N = 0 Then
    Which = "Enter your data in a vertical range of at least 4 cells. " _
      & String$(2, 10) _
      & "Top cell must contain the letter C or P, 2nd cell is the number " _
      & "of items in a subset, the cells below are the values from which " _
      & "the subset is to be chosen."
  Else
    Which = "This requires " & Format$(N, "#,##0") & _
      " cells, more than are available on the worksheet!"
  End If
  MsgBox Which, vbOKOnly, "DATA ERROR"
  Exit Sub
End Sub
Private Sub AddPermutation(Optional PopSize As Integer = 0, _
  Optional SetSize As Integer = 0, _
  Optional NextMember As Integer = 0)
  Static iPopSize As Integer
  Static iSetSize As Integer
  Static SetMembers() As Integer
  Static Used() As Integer
  Dim i As Integer
  If PopSize <> 0 Then
    iPopSize = PopSize
    iSetSize = SetSize
    ReDim SetMembers(1 To iSetSize) As Integer
    ReDim Used(1 To iPopSize) As Integer
    NextMember = 1
  End If
  For i = 1 To iPopSize
    If Used(i) = 0 Then
      SetMembers(NextMember) = i
      If NextMember <> iSetSize Then
        Used(i) = True
        AddPermutation , , NextMember + 1
        Used(i) = False
      Else
        SavePermutation SetMembers()
      End If
    End If
  Next i
  If NextMember = 1 Then
    SavePermutation SetMembers(), True
    Erase SetMembers
    Erase Used
  End If
End Sub  'AddPermutation
Private Sub AddCombination(Optional PopSize As Integer = 0, _
  Optional SetSize As Integer = 0, _
  Optional NextMember As Integer = 0, _
  Optional NextItem As Integer = 0)
  Static iPopSize As Integer
  Static iSetSize As Integer
  Static SetMembers() As Integer
  Dim i As Integer
  If PopSize <> 0 Then
    iPopSize = PopSize
    iSetSize = SetSize
    ReDim SetMembers(1 To iSetSize) As Integer
    NextMember = 1
    NextItem = 1
  End If
  For i = NextItem To iPopSize
    SetMembers(NextMember) = i
    If NextMember <> iSetSize Then
      AddCombination , , NextMember + 1, i + 1
    Else
      SavePermutation SetMembers()
    End If
  Next i
  If NextMember = 1 Then
    SavePermutation SetMembers(), True
    Erase SetMembers
  End If
End Sub  'AddCombination
Private Sub SavePermutation(ItemsChosen() As Integer, _
  Optional FlushBuffer As Boolean = False)
  Dim i As Integer, sValue As String
  Static RowNum As Long, ColNum As Long
  If RowNum = 0 Then RowNum = 1
  If ColNum = 0 Then ColNum = 1
  If FlushBuffer = True Or BufferPtr = UBound(Buffer()) Then
    If BufferPtr > 0 Then
      If (RowNum + BufferPtr - 1) > Rows.Count Then
        RowNum = 1
        ColNum = ColNum + 1
        If ColNum > 256 Then Exit Sub
      End If
      Results.Cells(RowNum, ColNum).Resize(BufferPtr, 1).Value _
        = Application.WorksheetFunction.Transpose(Buffer())
      RowNum = RowNum + BufferPtr
    End If
    BufferPtr = 0
    If FlushBuffer = True Then
      Erase Buffer
      RowNum = 0
      ColNum = 0
      Exit Sub
    Else
      ReDim Buffer(1 To UBound(Buffer))
    End If
  End If
  'construct the next set
  For i = 1 To UBound(ItemsChosen)
    sValue = sValue & ", " & vAllItems(ItemsChosen(i), 1)
  Next i
  'and save it in the buffer
  BufferPtr = BufferPtr + 1
  Buffer(BufferPtr) = Mid$(sValue, 3)
End Sub  'SavePermutation

 

[Nykyta]

Thank you very much michaelsmith559.
As I can see so far, that is all combination variant (3 of 8 = 56 combs). I'll try this code and try to catch how it could be done (if) when I exclude some rows.
I am using Excel from 2010 MSOffice.