Create all the combinations with some exceptions


New Member
Hey, i have a question for a university project:

There is a sheet and i want to figure out all possible combinations if you can only pick one "Option" of A, B and C.

Thats quite easy and the result will be:

1from the topopenbottom
3buttonsthin lineroled up


now the matter is that not all of the options of the colums can be combined e.g.: "a1 & c3" or "a2 & b2" so i want to filter out all of these "imposibble" combinations.

Is there any formula which can create the combinations from the first table without all the exceptions of a second table?

i would be very glad for any help and sorry if my question is not neccesarly precise enaugh - I am quite a beginner.



Well-known Member
Hi Valentin,

you'd probably need some formulas to get this done. See e.g. this function (rather complicated):
If you want to do it simpler, assuming you have in A1 the number of items in list 1, B1 the number of items in list 2 and C1 the number of items in list 3:

G1: =MOD(ROW()-1,$A$1)+1
H1: =INT(MOD(ROW()-1,$A$1*$B$1)/$A$1)+1
I1: =INT(MOD(ROW()-1,$A$1*$B$1*$C$1)/($A$1*$B$1))+1
J1: =G1&H1&I1 -> to combine them
And drag them down to get all combinations. The next step is to filter them. That filter list is a bit harder.

Say I have a filter list in E1:E4 with these values (? is a wildcard):
The filter formula for K1 would be:
=SUM(IFERROR(FIND($E$1:$E$4,J1),0)) -> array formula: put in cell and do CTRL+SHIFT+ENTER

Hope that gets you started,

Some videos you may like

This Week's Hot Topics