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

#### Nicky_G

##### New Member
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:

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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Fluff

##### MrExcel MVP, Moderator
Cross posted https://stackoverflow.com/questions...mns-permutation-with-limit-of-11-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.

#### Nicky_G

##### New Member
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?

#### pgc01

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

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.

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?

#### Nicky_G

##### New Member
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

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>
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

#### pgc01

##### MrExcel MVP
OK

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

- 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

?

#### pgc01

##### MrExcel MVP
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:

#### Nicky_G

##### New Member
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

#### pgc01

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

#### Nicky_G

##### New Member
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

1,106,042
Messages
5,508,963
Members
408,702
Latest member
daz457

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...