Create all the combinations with some exceptions

ruoss

New Member
Joined
Oct 7, 2019
Messages
1
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,257
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,621
Messages
5,445,548
Members
405,337
Latest member
kann82

This Week's Hot Topics

Top