While this is a really old question and Stefan found an enlightened answer back in the day, it could be worth a revisit. I have run into a need for such a codeless, dynamic Unpivot method myself and a Google search brought me here. Yes, Power Query does the job, but that is not exactly codeless in that there is a scripted background solution running in Power BI, it requires the user to Refresh the data (so, it is not automatic within the workbook) and it will not run on Excel for Mac (tmk).
The following is an approach based on Dynamic Arrays and using the LET function, so it will require Excel 2016 or Microsoft 365.
Let's say that Stefan's data are in cells A1 to N8. In Power Query parlance, we would say that Stefan wants to "Unpivot B1:N8 By A1:A8".
NB: The following approach would also accept multiple columns for By, e.g. you could have a need to "Unpivot D1:N8 By A1:C8".
=LET( unPivMatrix, B1:N8,
byMatrix, A1:A8,
upC, COLUMNS( unPivMatrix ),
byC, COLUMNS( byMatrix ),
dmxR, MIN( ROWS( unPivMatrix ), ROWS( byMatrix ) ) - 1,
upCells, dmxR * upC,
upSeq, SEQUENCE( upCells,, 0 ),
upHdr, INDEX( INDEX( unPivMatrix, 1, ), 1, SEQUENCE( upC ) ),
upBody, INDEX( unPivMatrix, SEQUENCE( dmxR ) + 1, SEQUENCE( 1, upC ) ),
byBody, INDEX( byMatrix, SEQUENCE( dmxR ) + 1, SEQUENCE( 1, byC ) ),
attr, INDEX( upHdr, MOD( upSeq, upC ) + 1 ),
mux, INDEX( upBody, upSeq/upC + 1, MOD( upSeq, upC ) + 1 ),
demux, IFERROR( INDEX(
IFERROR( INDEX( byBody,
IFERROR( INT( SEQUENCE( upCells, byC,0 )/byC/upC ) + 1, MOD( upSeq, upC ) + 1 ),
SEQUENCE( 1, byC + 1 ) ),
attr ),
upSeq + 1, SEQUENCE( 1, byC + 2 ) ),
mux ),
demux
)
How this works - Reading the inputs
The inputs are the range that you want to unpivot that I called unPivMatrix B1:N8 (which can be of any dimensions you need) and the columns that you want to unpivot them by that I called byMatrix A1:A8.
As a rule, the rows of the byMatrix need to be the same as the unPivMatrix, so you must start with A1 and not A2. I decided to take
this convention because their might be header in A1 and with some
small mods, this formula could produce exactly the same output as
Power Query, but that is not what Stefan asked for.
The formula first calculates the number of columns in each matrix: upC unpivot columns and byC by columns. From these it calculates dmxR (demultiplexed rows): how many rows of values will be delivered in the unpivot by taking the minimum of the rows of unPivMatrix and byMatrix and subtracting 1 because unPivMatrix has a header. Taking the MIN is an error prevention step in the event that the inputs have a different number of rows, which is an error by definition.
These values are used to create shaping variables that will be used later to form the output. upCells is the number of values that will be unpivoted and is used to generate an index pattern called upSeq that counts off the number of values from 0 which will used inside of the INDEX functions later. We start with 0 because upSeq will be modulated to form correct indexation of the inputs and outputs.
Now we will break down the parts of the matrices so that we can multiplex them. The parts look like this:
![enter image description here]()
There is an unpivot header (upHdr) that contains the identifier of the value data (in Stefan's case, GROUP-Admin, GROUP-SuperUser, etc.). These will be multiplexed into a column that will later be placed next to each value that is unpivoted. upHdr is created by putting the whole unPivMatrix into an INDEX function and reading Row 1 and all of the columns. I nested that INDEX function into another INDEX that reshapes the horizontal array to a vertical array using a vertical SEQUENCE of size upC.
upBody contains the values that we want to unpivot via multiplexing. It is created by putting the upMatrix into an INDEX and shaping it by the number of rows that will be output SEQUENCE( dmxR ) + 1 § against each of the columns to be read SEQUENCE( upC ).
byBody contains the data that will be multiplexed against each value in the upBody. It is created the same way as upBody.
§ - add 1 to skip the header row
How this works - Shaping and writing the outputs
The output will be shaped like this:
![enter image description here]()
We now multiplex the upHdr into attr or attributes (using Power Query terminology) by putting upHdr into an INDEX and applying a modulated sequence based on upSeq that repeats every upC times (e.g. {1;2;3;4;5;6;7;8;1;2;...} ). Note: this is where starting with 0 in upSeq mattered. The output of attr would look like (in Stefan's case) {GROUP-Admin; GROUP-SuperUser, etc.}.
mux is the values (using Power Query terminology) that will be multiplexed against each attribute and byBody row. It is created by putting valBody into INDEX and then reshaping it into a multiplex pattern created by rows of
upSeq/upC + 1 which yields a row of {1,1,1,1,1,1,1,1,2,2,...}
and columns of
MOD( upSeq, upC ) + 1 which yields a column of {1;2;3;4;5;6;7;8;1;...}.
The output for mux will be the content of the unPivMatrix. In Stefan's case, that will be a little special because he is using * and blank as data. This formula will convert blanks into 0's. So, if that is a problem, you could either wrap mux into an IF( ISBLANK( mux ), "", mux ), but I am not adding that because I want a general unpivot as I am sure Stefan has long since moved on.
How this works - Demultiplexing the parts into the output
Now that the easy part is done, it is time to go after the hard stuff - bringing this all together into a single dynamic array. Putting multiple arrays together requires a trick and this trick has to be applied twice because, as you can see, we are bringing three tables together. The trick is like having an APPEND function like:
APPEND( APPEND( table1, table2 ), table3 ).
To combine two arrays, you put the first array into an INDEX and then reference cells that are outside of the array to force a #REF! error. For example, if I have a 3 x 2 array of letters A through F and I reference cell 3, 3, it will throw a reference error.
![enter image description here]()
Now you can exploit the errors by replacing them with the table that you want to append by wrapping the INDEX in an IFERROR. It is kind of like:
IFERROR( INDEX( table1,
SEQUENCE( table1.rows ),
SEQUENCE( 1, table1.columns + table2.columns ) ),
table2 )
In this sense, the formula above is the equivalent of APPEND( table1, table2 ) where APPEND is a row-wise append of two tables which is what we want. (NB: switch the sequence patterns and you can make a column-wise append.)
So, hopefully this explanation makes it clear what is happening in the final stages of a variable called demux which delivers the result. I named the result and then referenced it so that you can easily explore, modify or optimize the formula. So, demux is really like:
APPEND( byBody, APPEND( attr, mux ) )
I won't go into the mechanics of how this final stage works because this is already a really long answer, but a short summary is that this append is using the dimensions created by upCells, upC and byC to form the output.
I've tested this, but I've not performance streamlined it or brought it to #SwissEngineering standards.
*with1first). – Claudianus