Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize')

user_3095042 0
Viewed 88391

I need to convert the Excel matrix FIRST in the table LATER:

FIRST:

    P1  P2  P3  P4
F1  X
F2  X   X
F3      X       X
F4      X   X

LATER:

F   P   VALUE
F1  P1  X
F1  P2
F1  P3
F1  P4
F2  P1  X
F2  P2  X
F2  P3
F2  P4
F3  P1
F3  P2  X
F3  P3
F3  P4  X
F4  P1
F4  P2  X
F4  P3  X
F4  P4
powerquery unpivot pivot-table matrix excel
5 Answers

All of the solutions so far involve VBA, PowerQuery, etc. which are great, but are "one-time" events. To make it more dynamic, consider using INDEX(MATCH(...)). This will allow for dynamic updates to the table.

enter image description here

user_106479260

The addition of the LET function & dynamic arrays allows for this non-VBA solution.

=LET(data,B2:E5,
     dataRows,ROWS(data),
     dataCols,COLUMNS(data),
     rowHeaders,OFFSET(data,0,-1,dataRows,1),
     colHeaders,OFFSET(data,-1,0,1,dataCols),
     dataIndex,SEQUENCE(dataRows*dataCols),
     rowIndex,MOD(dataIndex-1,dataRows)+1,
     colIndex,INT((dataIndex-1)/dataRows)+1,
     dataColumn, IF(INDEX(data,rowIndex,colIndex)="","",INDEX(data,rowIndex,colIndex)),
     unfiltered, CHOOSE({1,2,3},INDEX(rowHeaders,rowIndex),INDEX(colHeaders,colIndex), dataColumn),
     filtered, FILTER(unfiltered, dataColumn<>""),
     unfiltered)

This will show all items including those with blank data. To eliminate the blanks change the last parameter to filtered.

user_143854190

One more to add to the BoK. This requires Excel 365. It unpivots B1:E5 by A1:A5.

=LET( unPivMatrix, B1:E5,
      byMatrix, A1:A5,
        upC, COLUMNS( unPivMatrix ),
        byC, COLUMNS( byMatrix ),
        dmxR, MIN( ROWS( unPivMatrix ), ROWS( byMatrix ) ) - 1,
        dmxSeq, SEQUENCE( dmxR ) + 1,
        upCells, dmxR * upC,
        upSeq, SEQUENCE( upCells,, 0 ),
        upHdr, INDEX( INDEX( unPivMatrix, 1, ),  1,  SEQUENCE( upC ) ),
        upBody, INDEX( unPivMatrix,  dmxSeq,  SEQUENCE( 1, upC ) ),
        byBody, INDEX( byMatrix,  dmxSeq,  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 ),
        FILTER(demux, mux<>"")
 )

NB: the byMatrix can be a range with multiple columns and it will replicate the row values of the columns. e.g. you could have byMatrix of A1:C5 and unPivMatrix of D1:H5 and it would replicate the A2:C5 column values (ignoring A1).

user_46171210
Related