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

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

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    What I am looking for is impossible permutation combination I have been on this for sometime and cant seems to wrap a Excel vba around it. here goes.
    I have 5 columns each have a list of names( max limit of names per column is 50)

    1. First column a list of fruits
    2. Second column a list of vegetables
    3. Third column a list of nuts
    4. Fourth column a list of herbs
    5. Fifth column a list of spices

    each column will have a minimum and a maximum range and total output will have a Limit. So …

    • column 1: have min 1 max 3,
    • column 2: min 2 max 4,
    • column 3: min 3 max 4,
    • column 4: min 1 max 2,
    • column 5 min 0 max 0,
    • Total limit is 11

    So it have to pick any three names from first column and two names from second column and goes on till fifth. if a column is last column is empty it can skip work with only 4 columns. Then from all five column within the min and max defined limits names should total to a sum of 11 that means for example macro can choose 3 Fruits, 4 Vegetables, 2 Nuts, 2 Herbs, 0 Spice total to 11
    for example:

    • combi 1: Apple,Apricot,Avocado,fennel,potato,cauliflower,Garlic,Acorn,Almond,Anise,basil.
    • combi 2: Avocado,Banana,Bilberry,fennel,potato,cauliflower,Garlic,Acorn,Almond,Anise,basil.

    and combination should go on and on till all combinations are met it can be in 11 columns each combination can be a row so it can bring the maximum number of combinations if it can provide number of combinations before starting with a formula then we can edit or limit the list to accommodate the number of rows in excel from what I can see with 1 combination value 5,2,1,3,0 I can get upto 1100 combinations
    I have been up with this for a long time and the solution is not that great can anyone help me with this?
    Screenshot: Excel input table:

    Code I was working with so far the thing is not able to pick a range from list and set a total limit to 11

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,029
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

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

    Cross posted https://stackoverflow.com/questions/...1-in-vba-excel

    Cross-Posting
    While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
    This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Apr 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    No Body has answered in both Websites, Think this question is out of their depth. do we have any expert sites were i could seek professional help rather than people who criticize and check my grammar in both sites?

  4. #4
    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 Nicky_G View Post
    No Body has answered in both Websites, Think this question is out of their depth. do we have any expert sites were i could seek professional help rather than people who criticize and check my grammar in both sites?
    Hi Nicky
    Welcome to the board


    Since you are new here let me tell you that cross-posting without letting people know is very disrespectful.

    It means that I may be working on a solution for you and someone else has already done the same thing somewhere else.

    Meaning: I'm wasting my personal time trying to help you.



    It's OK to cross-post if you let people know that you posted the question somewhere else and post the links so that everyone can follow what's being done. In this case it's a bigger group of people working in collaboration, not doing redundant work.




    Now about your problem.



    It seems a simple combinations problem.


    I don't think that the values that you post in the table are adequate for testing.



    The sum of the minima (1+2+3+4+1) is 11 and the limit is 11.

    This means that there is only one combination possible in terms of food groups.



    I will use, therefore, as an example, another set of values, that will yield a more general solution.



    column 1 (F): have min 1 max 3,

    column 2 (V): min 1 max 4,

    column 3 (N): min 2 max 4,

    column 4 (H): min 2 max 3,

    column 5 (S): min 1 max 1,

    Total limit is 11



    Since in this case the limit is neither the sum of the minima or of the maxima there will be several combinations possible.



    I would solve it in 3 steps:



    1 - since the minima have to be respected I would start with a combination of all of them.

    In this case, this means that I would start with the combination (just using the initials of the food groups): FVNNHHS



    2 - since we already filled 7 positions, there remain 4 positions to be filled and we still have available 2F's, 3V's, 2N's and 1H.

    You can fill them choosing randomly from the available groups respecting the maxima.

    For ex., if the next element is an H then for the 3 next ones you only have F, V and N. The H is no longer available because now you have already 3H's in the combination which is the maximum allowed.



    The result will be for ex.: FFVVNNHHHS, FVVVNNHHHS, FVVNNNNHHS



    3 - Now that you have the total combination in terms of the food groups you can get for each food group a random combination of the respective food items.



    For ex., for the Fruits you have 2 F's and so you get 2 fruits from your fruit column, for ex. Apple and Banana. Do the same for the other food groups.



    Does this help?

    Please comment.

    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  5. #5
    New Member
    Join Date
    Apr 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi PGC ,

    Thanks for being polite, i do understand but the problem is still not solved across 10 sites and its been 30 day, no body was able to get close to some thing like this everyone says its impossible. i worked closer to a solution and my excel is out of row line to generate the records.

    I understand your theory but will explain so you can try, as only in two forum this post is alive. hope you could crack it all the best.

    the below are all possible combinations of sum 11 with just 4 columns
    3 2 1 5
    3 3 1 4
    4 1 1 5
    4 2 1 4
    4 3 1 3
    5 1 1 4
    5 2 1 3
    Take one for example 3215, in this case i should get 3 fruit names,2 veg names,1 nuts name, 5 spice name
    Ex:
    row 1 fruit1,fruit2,fruit3,veg1,veg2,nut1,spice1,spice2,spice3,spice4,spice5,
    row 2 fruit1,fruit2,fruit4,veg1,veg2,nut1,spice1,spice2,spice3,spice4,spice5,
    row 3 fruit1,fruit2,fruit5,veg1,veg2,nut1,spice1,spice2,spice3,spice4,spice5,
    row 4 fruit1,fruit3,fruit4,veg1,veg2,nut1,spice1,spice2,spice3,spice4,spice5,
    row 5 fruit1,fruit3,fruit5,veg1,veg2,nut1,spice1,spice2,spice3,spice4,spice5,
    row 6 fruit1,fruit4,fruit5,veg1,veg2,nut1,spice1,spice2,spice3,spice4,spice5,
    row 7 fruit2,fruit3,fruit4,veg1,veg2,nut1,spice1,spice2,spice3,spice4,spice5,

    like this it has to go on till it hits all combinations all unique value no duplicates(no fruit is repeated twice like fruit 1, fruit 1)

    i now understand, running the entire table is impossible in a single sheet so table 1 row 1 as input it have to continue below till hit last row then continue on to the second sheet. thanks for the help best of luck
    FYI: i tried semi manually so lots of hours wasted on this single combination.

    Regards
    Nick

  6. #6
    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

    OK

    I can try to post a solution tonight (GMT).

    Please clarify:

    - you want 1 random combination
    - you want a random list of a number of combinations, for ex. 10 random different combinations
    - you want a list of all possible combinations

    ?
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  7. #7
    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

    Hi

    Since just 1 combination might be not enough for your need and all the combinations possible might be millions which is also useless I post a solution for the case where you want a group of N random combinations of the food items that respect the minima and maxima of the food groups.

    Assuming a setup similar to the one you posted

    In in B2 the value of N, the total number of food items in the combinations

    In B3:F5 the food groups, the minima and the maxima

    In H2: down, the list of N valid combinations


    Insert a new module and paste:

    Code:
    Option Explicit
    
    Dim vIndex As Variant, vComb As Variant, vRest As Variant
    Dim vFI As Variant
    
    
    Sub Test()
    Dim r As Range
    Dim vResultFG As Variant
    Dim j As Long, N As Long, lCombs As Long
    
    N = Range("B2") 'limit
    Set r = Range("B3:F5") ' table with food group names, min, max
    ReDim vIndex(0 To r.Columns.Count - 1)
    vComb = vIndex
    vRest = vIndex
    
    ' get the values of the food items
    ReDim vFI(0 To r.Columns.Count - 1)
    For j = 0 To UBound(vFI)
        vFI(j) = Application.Transpose(Range(r(4, j + 1), r(3, j + 1).End(xlDown)).Value)
    Next j
    
    For j = 0 To UBound(vIndex)
        vComb(j) = r(2, j + 1)
        vRest(j) = r(3, j + 1) - r(2, j + 1)
        If vRest(j) <> 0 Then vIndex(j) = j Else vRest(j) = "#": vIndex(j) = "#"
        N = N - vComb(j)
    Next j
    
    ' Get group food Combinations
    lCombs = 15
    vResultFG = GetGFCombs(N, lCombs)
    
    ' get random food items for the group food combinations
    ReDim vresultFI(LBound(vResultFG) To UBound(vResultFG))
    For j = 0 To UBound(vResultFG)
       vresultFI(j) = GetFoodItems(vResultFG(j))
    Next j
    
    ' write the result
    Range("H3").Resize(lCombs).Value = Application.Transpose(vresultFI)
    End Sub
    
    ' get group food combinations
    Function GetGFCombs(ByVal N As Long, ByVal lCombs As Long) As Variant
    Dim j As Long, k As Long, lRow As Long, lPos As Long
    Dim vIndexT As Variant, vCombT As Variant, vRestT As Variant, vResult As Variant
    
    ReDim vResult(0 To lCombs - 1)
    Randomize
    For k = 0 To lCombs - 1
        vIndexT = vIndex: vCombT = vComb: vRestT = vRest
        For j = 1 To N
            vRestT = Filter(vRestT, "#", False)
            vIndexT = Filter(vIndexT, "#", False)
            lPos = Int((UBound(vIndexT) + 1) * Rnd)
            vCombT(vIndexT(lPos)) = vCombT(vIndexT(lPos)) + 1
            vRestT(lPos) = vRestT(lPos) - 1
            If vRestT(lPos) = 0 Then vRestT(lPos) = "#": vIndexT(lPos) = "#"
        Next j
        vResult(lRow) = vCombT
        lRow = lRow + 1
    Next k
    GetGFCombs = vResult
    End Function
    
    
    ' get food items for the combinations of food groups
    Function GetFoodItems(vResultFG As Variant) As Variant
    Dim v As Variant, v1 As Variant, s As String
    Dim i As Long, j As Long, k As Long, l As Long
    
    Randomize
    For j = 0 To UBound(vResultFG)
        v = Filter(vFI(j), "", True)
        ReDim v1(0 To vResultFG(j) - 1)
        For i = 1 To vResultFG(j)
            k = Int((UBound(v) + 1) * Rnd)
            For l = UBound(v1) - 1 To 0 Step -1
                If v1(l) <> "" Then If v1(l) > v(k) Then v1(l + 1) = v1(l) Else Exit For
            Next l
            v1(l + 1) = v(k)
            v = Filter(v, v(k), False)
        Next i
        s = s & ", " & Join(v1, ", ")
    Next j
    GetFoodItems = Mid(s, 3)
    End Function

    This is an example for 15 combinations, you can change the value of N if you want:




     ABCDEFGHIJK
    1         1 
    2Limit12         
    3Food groupFruitsVegetablesNutsHerbsSpice Cherry, Lychee, Melon, Capsicums, Eggplant, Bulk Nuts, Cashews, Soy Nuts, Chives, Peppermint, Tarragon, Turmeric 1 
    4Min12121 Blackberry, Melon, Peach, Brussels sprouts, Cauliflower, Cacao, Pecans, Pili Nuts, Cilantro, Parsley, Peppermint, Rosemary 1 
    5Max46341 Peach, Pear, Raspberry, Beans, Cauliflower, Cucumber, Fennel, Brazil Nuts, Pili Nuts, Lavender, Rosemary, Coriander Seed  1 
    6 OrangeBroccoliChestnutsBasilBay Leaves  Apple, Blackberry, Mango, Peach, Beetroot, Cauliflower, Cucumber, Cashews, Pili Nuts, Tarragon, Thyme, Bay Leaves  1 
    7 MangoBeetrootCacaoSagePeppercorns  Banana, Melon, Asparagus, Brussels sprouts, Capsicums, Black Walnuts, Bulk Nuts, Chestnuts, Chives, Oregano, Sage, Cayenne Pepper  1 
    8 AppleCapsicumsPili NutsTarragonCoriander Seed  Lychee, Carrots, Celeriac, Cucumber, Eggplant, Cashews, Soy Nuts, Chives, Oregano, Parsley, Tarragon, Coriander Seed  1 
    9 LycheeFennelBulk NutsThymeThyme Banana, Mango, Brussels sprouts, Celery, Cucumber, Cacao, Pili Nuts, Cilantro, Parsley, Rosemary, Thyme, Bay Leaves  1 
    10 PeachBeansMacadamia NutsChivesRosemary Melon, Orange, Beetroot, Brussels sprouts, Fennel, Black Walnuts, Bulk Nuts, Cashews, Basil, Lavender, Tarragon, Cayenne Pepper  1 
    11 PearEggplantCashewsCilantroCayenne Pepper  Lychee, Beetroot, Carrots, Celeriac, Cucumber, Fennel, Black Walnuts, Soy Nuts, Basil, Chives, Cilantro, Thyme 1 
    12 BlackberryCauliflowerPine NutsLavenderTurmeric Blackberry, Mango, Celeriac, Cucumber, Black Walnuts, Cashews, Pecans, Cilantro, Oregano, Parsley, Thyme, Bay Leaves  1 
    13 MangoBrussels sproutsBlack WalnutsOregano  Banana, Melon, Peach, Broccoli, Carrots, Black Walnuts, Macadamia Nuts, Pecans, Chives, Lavender, Rosemary, Cayenne Pepper    
    14 RaspberryCelerySoy NutsParsley  Lychee, Asparagus, Beans, Capsicums, Celeriac, Celery, Cacao, Macadamia Nuts, Soy Nuts, Rosemary, Tarragon, Thyme   
    15 MelonCucumberPecansPeppermint  Lychee, Mango, Pear, Broccoli, Brussels sprouts, Celeriac, Chestnuts, Pili Nuts, Chives, Oregano, Peppermint, Peppercorns    
    16 BananaAsparagusBrazil NutsRosemary  Banana, Lychee, Orange, Beetroot, Broccoli, Fennel, Cashews, Oregano, Parsley, Tarragon, Thyme, Turmeric   
    17 CherryCeleriac    Banana, Cherry, Melon, Beans, Capsicums, Eggplant, Black Walnuts, Pine Nuts, Chives, Rosemary, Thyme, Rosemary   
    18  Carrots        
    19           
    [FoodCombs.xlsm]Sheet1
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  8. #8
    New Member
    Join Date
    Apr 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    This is really good i appreciate the effort gone through. In the case without Min max just one single combination example 3 2 1 5 (limit or sum of 3+2+1+5 at 11) would its self will yield 100000 records in total if you try mathematically, but implementing the same we are not reaching there. will try something today, meanwhile if you too get something please share.

    Thanks a Ton
    Nick

  9. #9
    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 Nicky_G View Post
    In the case without Min max just one single combination example 3 2 1 5 would its self will yield 100000 records in total if you try mathematically, but implementing the same we are not reaching there. will try something today, meanwhile if you too get something please share.
    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?
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  10. #10
    New Member
    Join Date
    Apr 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    I have 5 columns each have a list of names


    1. First column a list of fruits
    2. Second column a list of vegetables
    3. Third column a list of nuts
    4. Fourth column a list of herbs


    each column will have a defined range and total output will be 11


    • column 1: range 3,
    • column 2: range 2,
    • column 3: range 1,
    • column 4: range 5,
    • Total limit is 11


    So it have to pick any three names from first column and two names from second column and goes on till fourth with a limit on total names should be a sum of 11 that means for example macro can choose 3 Fruits, 2 Vegetables, 1 Nuts, 5 Herbs total to 11 simislarly it have to choose all combination of 3,2,1,5 all all fruits,veg,nuts,herbs in 4 columns but no duplicates
    for example:


    • combi 01: FRUIT1,FRUIT2,FRUIT3,VEG1,VEG2,NUT1,HERB1,HERB2,HERB3,HERB4,HERB5
    • combi 02: FRUIT1,FRUIT2,FRUIT4,VEG1,VEG2,NUT1,HERB1,HERB2,HERB3,HERB4,HERB5
    • combi 03: FRUIT1,FRUIT2,FRUIT5,VEG1,VEG2,NUT1,HERB1,HERB2,HERB3,HERB4,HERB5
    • combi 04: FRUIT1,FRUIT2,FRUIT6,VEG1,VEG2,NUT1,HERB1,HERB2,HERB3,HERB4,HERB5
    • combi 05: FRUIT1,FRUIT3,FRUIT4,VEG1,VEG2,NUT1,HERB1,HERB2,HERB3,HERB4,HERB5
    • combi 06: FRUIT1,FRUIT3,FRUIT5,VEG1,VEG2,NUT1,HERB1,HERB2,HERB3,HERB4,HERB5
    • combi 07: FRUIT1,FRUIT3,FRUIT6,VEG1,VEG2,NUT1,HERB1,HERB2,HERB3,HERB4,HERB5
    • combi 08: FRUIT2,FRUIT3,FRUIT4,VEG1,VEG2,NUT1,HERB1,HERB2,HERB3,HERB4,HERB5
    • combi 09: FRUIT2,FRUIT3,FRUIT5,VEG1,VEG2,NUT1,HERB1,HERB2,HERB3,HERB4,HERB5
    • combi 10: FRUIT2,FRUIT3,FRUIT6,VEG1,VEG2,NUT1,HERB1,HERB2,HERB3,HERB4,HERB5
    • combi 11: FRUIT3,FRUIT4,FRUIT5,VEG1,VEG2,NUT1,HERB1,HERB2,HERB3,HERB4,HERB5
    • combi 12: FRUIT3,FRUIT4,FRUIT6,VEG1,VEG2,NUT1,HERB1,HERB2,HERB3,HERB4,HERB5
    • combi 13: FRUIT4,FRUIT5,FRUIT6,VEG1,VEG2,NUT1,HERB1,HERB2,HERB3,HERB4,HERB5
    • combi 14: FRUIT1,FRUIT2,FRUIT3,VEG1,VEG3,NUT1,HERB1,HERB2,HERB3,HERB4,HERB5
    • combi 15: FRUIT1,FRUIT2,FRUIT3,VEG2,VEG3,NUT1,HERB1,HERB2,HERB3,HERB4,HERB5
    • combi 16: FRUIT1,FRUIT2,FRUIT3,VEG1,VEG2,NUT2,HERB1,HERB2,HERB3,HERB4,HERB5
    • combi 17: FRUIT1,FRUIT2,FRUIT3,VEG1,VEG2,NUT3,HERB1,HERB2,HERB3,HERB4,HERB5


    AND GOES ON..... TILL ALL COMBINATION WITHOUT DUPLICATES LIKE (FRUIT2,FRUIT2,FRUIT3....)


    GOOD LUCK/....

    Regards
    Nick

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
  •