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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top