Forum Discussion
Excel data split from multiple column to row
Here are the formulas in a version you can paste and fill down:
=IF(MOD(ROW()-ROW($D$7),COLUMNS($B$1:$K$4)),"",INDEX($A$1:$A$4,INT((ROW()-ROW($D$7))/COLUMNS($B$1:$K$4))+1,1))=INDEX($A$1:$K$4,INT((ROW()-ROW($D$7))/COLUMNS($B$1:$K$4))+1,MOD(ROW()-ROW($D$7),COLUMNS($B$1:$K$4))+2)you can either replace the ranges like $B$1:$K$4 with the correct range in each case or I would recommend replacing it with a named variable (e.g. DATA) then you create in the Name Manager under the Formula menu. BTW the $D$7 in these formulas are the first cell where the data will go so that ROW()-ROW($D$7) gives you a count.
Alternatively with Power Query. Sample attached
- I have large data in similar form
Here are the formulas in a version you can paste and fill down:
=IF(MOD(ROW()-ROW($D$7),COLUMNS($B$1:$K$4)),"",INDEX($A$1:$A$4,INT((ROW()-ROW($D$7))/COLUMNS($B$1:$K$4))+1,1))=INDEX($A$1:$K$4,INT((ROW()-ROW($D$7))/COLUMNS($B$1:$K$4))+1,MOD(ROW()-ROW($D$7),COLUMNS($B$1:$K$4))+2)you can either replace the ranges like $B$1:$K$4 with the correct range in each case or I would recommend replacing it with a named variable (e.g. DATA) then you create in the Name Manager under the Formula menu. BTW the $D$7 in these formulas are the first cell where the data will go so that ROW()-ROW($D$7) gives you a count.
mtarler what needs to do if I want output like
I have large data in similar form => PQ well design for this kind of task
not much familiar with query, can you guide:
- Format your range https://support.microsoft.com/en-us/office/format-an-excel-table-6789619f-c889-495c-99c2-2f971c0e2370
- Go to Data tab > From Table/Range (Power Query Editor opens)
- Delete "Change Type" in APPLIED STEPS
- Right-click on column [Resource] > Unpivot Other Columns
- Delete column [Attibute]
- Close & Load (top left of the window)
Any problem please attach your workbook
- Thanks for guide it works.
Lorenzo not much familiar with query, can you guide 🙂
