Using the Power of two to find which options are selected in a recursive fashion

I have a list of options which each has a unique value in the form of Power of Two, 0-24 all corresponding to an option:
Power of two options.png
I also have a list with a number, ("Moduler"), corresponding to one or more of these options.
List of options.png
I would like to for each such number break out which options has been set.
I am thinking on the lines of a recursive calclulation in each column, but I am not sure as how to do it properly. Lowest and highest value is fine, but it is the options in between which are difficult.

Any Ideas here?

See attached file "Lisenser_perland_20210128.xlsx" for none sensetive information.

I would not use powers of two, but rely on decades.

So your first set of options are 1-9, the second are 10, 20, 30 --- 90, the third are 100, 200, 300, ---- 900 When you add them, you get a number that uniquely specifies the selections, like

732 7th from the third set, 3rd from the second set, and 2nd from the first set.

See sheet1: is this what you require where "1" indicates a selection ? (Illustration only!)

"Moduler" value in A1

Please Login or Register  to view this content.

NOTE: heading in the 2 sheets are not consistent in order or content

UPDATED with BD change

Nice code - but you might want <= instead of just less than, to capture the last value.

@Bernie: thank you.

Here's a formula solution, based on John Topley's logic:

In A4, enter

=IF(A2<=$A$6-SUM(B4:$Z4),A2,0)

And copy to B4:Y4. Enter the sum of the values into A6, and the numbers that sum to that value will be shown in row 4.

Originally Posted by J_Andreas_Lindqvist

the power of two is nothing I can change, that is how the options are structured in the source

Good choice, IMHO. But that is limited to 53 options.

(Excel does not format the sum with option 2^50, 2^51 and 2^52 correctly. But they are accounted for correctly.)

Enter the following into D2 and copy across into E2:T2:

=IFERROR(IF(ISODD(INT([@Moduler]/2^(MATCH(D$1,'VB Moduler'!$A$1:$Y$1,0)-1))),1,""),"")

The structured Table automagically copies the formulas down their respective columns.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.