# Thread: Impossible: 4 or 5 columns permutation with limit of 11 in VBA Excel Thanks:  1 Post #5062700 (1) Likes:  3 Post #5062700 (2)Post #5059904 (1)

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

Originally Posted by pgc01
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.
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