Convert Horizontal Table Info in Vertical Table With Formulas

Question

I have multiple rows of data for different users. Some users have multiple entries and some will only have one. All this info is stored in a '1 row per user' format. My goal is to show all this info vertically, with each user having potentially multiple lines based on how many parameters they pass.

What is the best way to go about this? Ideally, I'd like to have it set up where I can just paste new user entry rows at the bottom and have the vertical table repopulate.

I'm using O365

As @SolarMike says, it has been done before and I thought that I had done it once. Not quite, but this is similar. https://stackoverflow.com/questions/68695983/excel-udf-to-unpivot-melt-reverse-pivot-flatten-normalize-blocks-of-data-wi/68696124#68696124 — mark fitzpatrick, Jan 15 '22 at 08:08

bosco_yip · Answer 1 · 2022-01-16T00:06:02.543

3

This formula work for Excel 2019 and up

In Cell I2 array (CSE) formula copied across right to K2, and all copied down :

  =IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(" ",,IF(ISNUMBER($B$2:$G$4),$A$2:$A$4&" "&$B$2:$G$4,"")&IF(ISTEXT($B$2:$G$4),$B$2:$G$4,""))," ","</b><b>")&"</b></a>","//b["&(ROW($A1)*3+COLUMN(A$1))-3&"]"),"")

Remark : In Office 365, I think it is a normal entry.

edited Jan 16 '22 at 00:06

answered Jan 15 '22 at 10:59

bosco_yip

3,762
2
5
10

Nice work! Incredibly clever use of FILTERXML. – mark fitzpatrick Jan 15 '22 at 12:08
Amazing formula, thank you! – Phil Jan 16 '22 at 02:55

score 2 · Accepted Answer · answered Jan 15 '22 at 10:33

Here is a modified version of the answer in this post:

=LET( upValues, B2:G5, upFields, {"ID","Color"},
      byBody, A2:A5,  byHdr, A1,

       blockSize, COLUMNS( upFields ),
       byC, COLUMNS( byBody ), upC, COLUMNS( upValues ),
       dmxR, MIN( ROWS( upValues ), ROWS( byBody ) ),
       upCells, dmxR * upC/blockSize,
       tCSeq, SEQUENCE( 1, byC + blockSize ),  tRSeq, SEQUENCE( upCells + 1,, 0 ),  upSeq, SEQUENCE( upCells,, 0 ),

       hdr, IF( tCSeq <= byC,  INDEX( byHdr, , tCSeq ),
                               INDEX( upFields, 1, tCSeq - byC - 0 ) ),
       muxBody, INDEX( byBody, SEQUENCE( upCells, byC, 0 )/byC/upC*blockSize + 1, SEQUENCE( 1, byC ) ),
       muxValues, INDEX( upValues, SEQUENCE( upCells, blockSize, 0 )/upC+1, MOD(SEQUENCE( upCells, blockSize, 0 ),upC)+1),

       table, IF( tCSeq <= byC, muxBody,
                                INDEX( muxValues, upSeq + 1, tCSeq - byC ) ),
       ftable, FILTER( table, INDEX( table, , 2 ) <> 0 ),
       fRSeq, SEQUENCE( ROWS( ftable ) + 1,,0 ),

       IF( fRSeq = 0, hdr, INDEX( ftable, fRSeq, tCSeq) )  )

When unpivoting, we say that we Unpivot rangeX By rangeY. This formula follows that convention.

upValues is the values to be unpivoted (B2:G5 in this example). This accepts a range.

upFields are the column headers that you want to display in your output (I hard coded these in because ID# - 1, 2, etc. are essentially ignored in your case). This accepts a row-wise range or typed-in row-wise array as shown.

byBody is the column of by values (Jim, Mike...). This accepts a column-wise range

byHdr is the header for the by column(s). (Name is only one column, but this accepts a row-wise range)

As I assume that you want to apply it more generally, I left in a lot of the error handling overhead that was in the original post.

Convert Horizontal Table Info in Vertical Table With Formulas

2 Answers2