Permutations without repetition using options in categories

Dear all,

I am trying to generate a list of scenario's. It is similar to the problem described in http://www.get-digital-help.com/2015...-combinations/
where you try to make a pizza using several ingredients.

The difference is there are several categories, and in each category several options. For example

Cheese
- goat cheese
- cheddar cheese
- parmesan cheese

Vegetable
- mushroom
- unions
- peppers

sauses
- tomato
- spicy
- bacon

Scenario's would be [tomato, mushroom, goatcheese], [tomato, mushroom, cheddar cheese] and [spicy, mushroom, goat cheese].

You can only select one saus, one vegetable and one cheese. I need to have all the scenario's listed for further use. How do I list all these different scenario's? Is this possible using the formulas? I really prefer not to use VBA or macro as I have never looked into it and don't exactly know what they are.

Thanks in advance!

You can do this with named ranges and data validation

See attached:

Columns A, b and C provide the permuations

A

=INT((ROWS($1:3)-1)/9)+1

B

=MOD(INT((ROWS($1:3)-1)/3),3)+1

C

=MOD((ROWS($1:3)-1),3)+1

Columns E to G are your ingredients

in I:J your pizzas

in I2

=INDEX(E$2:E$4,A2)

Copy across to K and down

Dear PFDave and JohnTopley,

Thank you for helping me out. Is it correct you both have the same file attached?
John, I don't see how you used the formulas you mentioned and I am not able to produce it myself based on your instructions.

The attached file does not show how I can list al the possible options. As I mentioned I need the full overview of all the options. Could you please clarify what you mean?

Thanks again!

This may be what you require, along the same lines as Johns answer

Regards

Dav

Columns A to C give you all the permutations of ingredients.

It would have helped if I had attached the correct file: see attached.

Thanks guys! This really helps. Both of your answers do the trick. I am trying to understand what you did and see if I can work with it. I was looking at both your examples how it would work if I want to add parameters of options in the category.
Could you explain to me some things in the formulas?

John:

=INT((ROWS($1:1)-1)/9)+1 What is the information selected? Why 9?

=MOD(INT((ROWS($1:1)-1)/3);3)+1 I understand the first "3" is the position as in 1/3 or 2/3. What is the second 3?

=MOD((ROWS($1:1)-1);3)+1 same question and why the +1?

Davsth:

From your example it is more clear on where the formule gets the information. And I was able to add options and categories, I just can't get the positions right, so I end up with several zero's. Also, I am getting the same option several times, and I need every permutation to be unique.

I've taken your file and removed the blank columns, and then inserted a new row 1 and a few more blank rows below your data so that you can add or remove items from rows 3 to 13 to give you some flexibility, then I put this formula in B1:

=COUNTA(B3:B13)

which can be copied across to F1 to return the number of options in each category. This formula in A1:

=PRODUCT(B1:F1)

tells you how many combinations you can expect (i.e. how many rows you need to copy the formulae down, before they start to repeat). Starting with F16, I used this formula:

=INDEX(F$3:F$13,MOD(ROWS($1:1)-1,F$1)+1)

and if you copy this down a few rows you will see that it returns the items from the range F3:F13, but as there are only 5 of them then it returns the first one again after the fifth one, and so on down the column. Notice that 1 is subtracted from the ROWS($1:1) term (resulting in zero), and the MOD term will thus return the values 0, 1, 2, 3, 4 then 0 again as the value of the ROWS term increases, so the 1 needs to be added back in to give the elements of the array F3:F13 that you want to return (i.e. elements 1, 2, 3, 4, and 5). I used this formula in E16:

=INDEX(E$3:E$13,MOD(INT((ROWS($1:1)-1)/PRODUCT(F$1:$F$1)),E$1)+1)

and if you copy this down a few rows you will see that it returns the first item from E3:E13 for as many times as there are items in F3:F13 (i.e. 5 times), and then starts to return the second item from E3:E13, and so on. Here the ROWS($1:1)-1 term is divided by F1, and then we take the integer value, so there will be 5 zeros, then 5 ones, and so on. These are then passed in turn to the MOD function, which returns the remainder after division, and then by adding the 1 back in we will get a series of ones, then twos, then threes etc., before wrapping back to zeroes after the number of times given by E1. Strictly speaking, the /PRODUCT(F$1:$F$1) term is not needed in this particular formula, as it could be replaced by /F$1, but it makes it easier for the other formulae, as the formula in E16 can just be copied into D16, C16 and B16, and then all the formulae in B16:F16 can be copied down for the number of rows given in cell A1 (i.e. 5250 rows with this set up).

Hopefully you will find this approach a bit easier to follow (and it doesn't use volatile functions).

Hope this helps.

Pete

Thanks Pete,

This works very nice and thanks for the explanation.

Thanks to all of you again for the help, you are very kind!

Best, Natasja

You're welcome - thanks for feeding back.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete