Create all the combinations with some exceptions

ruoss

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


ABC
1from the topopenbottom
2valcroclosedtop
3buttonsthin lineroled up

<tbody>
</tbody>


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

Well-known Member
Hi Valentin,

you'd probably need some formulas to get this done. See e.g. this function (rather complicated): https://stackoverflow.com/questions/48651400/how-to-list-all-possible-combinations-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
 

Some videos you may like

This Week's Hot Topics

Top