Is there formala in excel or google spreed sheet can convert a table to a list like this?
from this table:
to this list:
You may try any one of the following approaches, as per your Excel version.
• 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.