Create all the combinations with some exceptions

[ruoss]

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:
a1,b1,c1;a1,b1,c2;a1,b1,c2...


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]from the top[/TD]
[TD]open[/TD]
[TD]bottom[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]valcro[/TD]
[TD]closed[/TD]
[TD]top[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]buttons[/TD]
[TD]thin line[/TD]
[TD]roled up[/TD]
[/TR]
</tbody>[/TABLE]


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.

Thanks,
Valentin

 

[Rijnsent]

Hi Valentin,

you'd probably need some formulas to get this done. See e.g. this function (rather complicated): https://stackoverflow.com/questions...tions-of-the-values-in-three-columns-in-excel
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):
12?
21?
1?3
3?2
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,
Koen