2
$\begingroup$

I am layering, stacking my options in excel. I have asked the question in a similar way, however I now want to put some more detail into it. If I have n number of boxes to stack, the possible options to stack them is 2^n-1. Let me give an example of 3 boxes and we give them the names A, B, C and D. The way they are stacked does not matter, meaning AB=BA and ABC=CAB, they count as 1 stack option. The result would be:

A, B, C, AB, BC, AC, ABC

Now I would like to create an excel file which in which I will enter the boxes letters and it gives me a list of all the possibilities for stacking. So I would provide the number of boxes and the letters. (3 boxes, A, B, C) Excel reads this in and gives me in cells the options.

Is this possible? Can anyone help me with this?

Thank you in advanced!

1 Answers 1

0

This will do it:

In the top row leave the first two cells empty and then put the numbers $1$ to $n$ in the following cells to the right starting at cell C1.

In the second row first cell A2 put the string ABC or whatever it is. In the third cell put =MID($A2,C$1,1) and then copy this under all the numbers to the right.

In the third row put $2^n+1$ in cell A3, then below it $2^n+2$ and so on down all the way to $2^{n+1}-1$.

Put =DEC2BIN(A3) in cell B3 then copy this down next to all the numbers on the left.

Put =IF(MID(B3,C$1+1,1)="1",C$2,"") in cell C3 then copy this down and across as far as necessary

In the third row, to the right of where you have formulae, put =C3&D3&E3 or `=CONCATENATE(C3,D3,E3) or whatever is needed to include $n$ terms then copy this down as far as necessary.

This final column is what you want. If you start with $n=3$ and your string is "ABC" then your spreadsheet should look something like

            1   2   3    ABC         A   B   C    9   1001            C   C 10  1010        B       B 11  1011        B   C   BC 12  1100    A           A 13  1101    A       C   AC 14  1110    A   B       AB 15  1111    A   B   C   ABC