Impossible: 4 or 5 columns permutation with limit of 11 in VBA Excel
Page 2 of 2 FirstFirst 12
Results 11 to 11 of 11

Thread: Impossible: 4 or 5 columns permutation with limit of 11 in VBA Excel

  1. #11
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,672
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Impossible: 4 or 5 columns permutation with limit of 11 in VBA Excel

    Quote Originally Posted by pgc01 View Post
    Hi

    This is much simpler.
    If I understand correctly you just have to calculate the combinations of each food group and concatenate them.

    What I don't understand is why you (or anyone) would want to write a list of hundreds of thousands of combinations.
    It seems pointless, like those posts that ask to write all the lotto combinations.
    Can you please explain what your objective is?
    Hi Nick

    You did not answer why you'd want a list with hundreds of thousands of combinations.

    This is a small program, however, and I had some free time yesterday and I did it anyway.

    Still curious, what are you going to do with this?

    With a setup similar to yours
    - row 3 with the food group names
    - below the name the number of food elements of that group in the combination
    - below the number the list of food elements to choose from

    The result in H3, down

    Insert a new module and paste:


    Code:
    Option Explicit
    Option Base 1
    
    Dim vFICount As Variant
    Dim vFI As Variant, vResult As Variant
    
    Sub Test()
    Dim r As Range
    Dim vFICountT As Variant
    Dim j As Long, lRow As Long
    
    Set r = Range("B3:F4") ' table with food group names and food items count for each combination
    vFICount = Application.Index(r.Rows(2).Value, 0)
    
    ' get the values of the food items
    ReDim vFI(1 To r.Columns.Count)
    ReDim vFICountT(1 To r.Columns.Count)
    For j = 1 To UBound(vFI)
        vFI(j) = Application.Transpose(Range(r(3, j), r(2, j).End(xlDown)).Value)
        If Not IsArray(vFI(j)) Then vFI(j) = Array(vFI(j))
        vFICountT(j) = UBound(vFI(j))
    Next j
    ReDim vResult(1 To Application.Product(Application.Combin(vFICountT, vFICount)), 1 To 1)
    
    
    ' get the combinations
    comb "", 1, 1, 1, lRow
    
    ' write the result
    Range("H3").Resize(UBound(vResult)).Value = vResult
    End Sub
    
    Sub comb(ByVal sComb As String, ByVal lFI As Long, ByVal lPos As Long, ByVal lInd As Long, ByRef lRow As Long)
    Dim j As Long, s As String
    
    For j = lInd To UBound(vFI(lFI))
        s = sComb & ", " & vFI(lFI)(j)
        If lPos = vFICount(lFI) Then
            If lFI = UBound(vFICount) Then
                lRow = lRow + 1
                vResult(lRow, 1) = Mid(s, 3)
            Else
                comb s, lFI + 1, 1, 1, lRow
            End If
        Else
            comb s, lFI, lPos + 1, j + 1, lRow
        End If
    Next j
    End Sub

    Ex.:



     ABCDEFGHI
    1         
    2         
    3Food groupFruitsVegetablesNutsHerbsSpice Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Basil, Sage, Bay Leaves , Peppercorns , Coriander Seed , Smoked Paprika  
    4n53124 Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Basil, Sage, Bay Leaves , Peppercorns , Coriander Seed , Ground Cumin  
    5 OrangeBroccoliChestnutsBasilBay Leaves  Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Basil, Sage, Bay Leaves , Peppercorns , Smoked Paprika , Ground Cumin  
    6 MangoBeetrootCashewsSagePeppercorns  Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Basil, Sage, Bay Leaves , Coriander Seed , Smoked Paprika , Ground Cumin  
    7 AppleCapsicums TarragonCoriander Seed  Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Basil, Sage, Peppercorns , Coriander Seed , Smoked Paprika , Ground Cumin  
    8 LycheeFennel ThymeSmoked Paprika  Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Basil, Tarragon, Bay Leaves , Peppercorns , Coriander Seed , Smoked Paprika  
    9 Peach   Ground Cumin  Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Basil, Tarragon, Bay Leaves , Peppercorns , Coriander Seed , Ground Cumin  
    10 Pear     Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Basil, Tarragon, Bay Leaves , Peppercorns , Smoked Paprika , Ground Cumin  
    11 Blackberry     Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Basil, Tarragon, Bay Leaves , Coriander Seed , Smoked Paprika , Ground Cumin  
    12 Mango     Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Basil, Tarragon, Peppercorns , Coriander Seed , Smoked Paprika , Ground Cumin  
    13 Raspberry     Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Basil, Thyme, Bay Leaves , Peppercorns , Coriander Seed , Smoked Paprika  
    14 Melon     Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Basil, Thyme, Bay Leaves , Peppercorns , Coriander Seed , Ground Cumin  
    15 Banana     Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Basil, Thyme, Bay Leaves , Peppercorns , Smoked Paprika , Ground Cumin  
    16       Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Basil, Thyme, Bay Leaves , Coriander Seed , Smoked Paprika , Ground Cumin  
    17       Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Basil, Thyme, Peppercorns , Coriander Seed , Smoked Paprika , Ground Cumin  
    18       Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Sage, Tarragon, Bay Leaves , Peppercorns , Coriander Seed , Smoked Paprika  
    19       Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Sage, Tarragon, Bay Leaves , Peppercorns , Coriander Seed , Ground Cumin  
    20       Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Sage, Tarragon, Bay Leaves , Peppercorns , Smoked Paprika , Ground Cumin  
    21       Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Sage, Tarragon, Bay Leaves , Coriander Seed , Smoked Paprika , Ground Cumin  
    22       Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Sage, Tarragon, Peppercorns , Coriander Seed , Smoked Paprika , Ground Cumin  
    23       Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Sage, Thyme, Bay Leaves , Peppercorns , Coriander Seed , Smoked Paprika  
    24       Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Sage, Thyme, Bay Leaves , Peppercorns , Coriander Seed , Ground Cumin  
    25       Orange, Mango, Apple, Lychee, Peach, Broccoli, Beetroot, Capsicums, Chestnuts, Sage, Thyme, Bay Leaves , Peppercorns , Smoked Paprika , Ground Cumin  
    [FoodCombs.xlsm]Sheet3
    Last edited by pgc01; May 22nd, 2018 at 08:03 AM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •