# 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...

 A B C 1 from the top open bottom 2 valcro closed top 3 buttons thin line roled 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

### 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
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