Forum Discussion
Solved
Excel Table format change
Hi, I need to convert a table format as per picture below (table with hundreds of lines). Any suggestion to automate this kind of table transformation? Many Thanks. Joaquim
You can use Power Query for this.
Click anywhere in the table.
On the Data tab of the ribbon, in the Get & Transform Data group, click From Table/Range.
In the Power Query Editor, activate the Transform tab of the ribbon.
Click the drop-down arrow on the right of Unpivot Columns, and select Unpivot Other Columns.
Select the Attribute column, right-click in it and select Remove from the context menu.
Double-click Value and change it to Contact.
Activate the Home tab of the ribbon.
Click Close & Load > Close & Load to..., and select a destination.
Excel 365 Version
Data starts from A1
You can use the below formula
LET(TBL,TEXTSPLIT(TEXTJOIN(",",TRUE,(TOCOL(A2:A4&"|"&B2:D4,0,FALSE))),"|",","),FILTER(TBL,CHOOSECOLS(TBL,2)<>""))
Data starts from A1
You can use the below formula
LET(TBL,TEXTSPLIT(TEXTJOIN(",",TRUE,(TOCOL(A2:A4&"|"&B2:D4,0,FALSE))),"|",","),FILTER(TBL,CHOOSECOLS(TBL,2)<>""))