excelexcel-formulaspreadsheet

How to convert a table to a list with row & column header

Is there formala in excel or google spreed sheet can convert a table to a list like this?

from this table:

Table

to this list:

List

Solution

  • You may try any one of the following approaches, as per your Excel version.

    FORMULA_SOLUTION

    • If you are using Excel 2010/2013/2016/2019/2021/MS365, then this one works for all

    Formula used in cell A6

    =INDEX($A$2:$A$4,INT((ROW()-6)/COLUMNS($B$1:$D$1))+1)

    Formula used in cell B6

    =INDEX($B$1:$D$1,MOD((ROW()-6),ROWS($A$2:$A$4))+1)

    Formula used in cell C6

    =INDEX($B$2:$D$4,MATCH(A6,$A$2:$A$4,0),MATCH(B6,$B$1:$D$1,0))

    Note: You may need to change the (ROW()-6) depending on the ROW() from where you are entering the formula for transformation. Example, like if you start from cell or row 3 then it will be (ROW()-3) so on so forth accordingly as per your suit.

    • If you are using MS365 and when writing if you have enabled the Office Insiders Beta Channel Version then, using LAMBDA() HSTACK() TEXTSPLIT() DROP() TAKE() TOCOL() & TEXTJOIN() can achieve it as well.

    =LAMBDA(array,
HSTACK(TEXTSPLIT(TEXTJOIN(".",1,
DROP(TAKE(array,,1)&"-"&TAKE(array,1),1,1)),"-","."),
TOCOL(DROP(array,1,1))))(A1:D4)

    • Also Note : You can use a custom, reusable formula with a friendly name by placing the above formula in the Name Manager - with Define Name as UNPIVOT.

    So from Formulas tab, Click on Define Name --> Enter the name as UNPIVOT and place the above formula as in refers to

    =LAMBDA(array,
HSTACK(TEXTSPLIT(TEXTJOIN(".",1,
DROP(TAKE(array,,1)&"-"&TAKE(array,1),1,1)),"-","."),
TOCOL(DROP(array,1,1))))

    The syntax is

    =UNPIVOT(array)

    Where array is A1:D4

    Closing Note: You can also achieve the above transformation using Power Query as well.