Forum Discussion
Excel data split from multiple column to row
Hi All,
Can you suggest how I can move data from multiple columns to rows below one another.
below is example how i needsample requirement
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.
16 Replies
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.
Lorenzo not much familiar with query, can you guide 🙂
Sumit_Bhokare If this is a one time thing you can just use the paste options to transpose each row of data. But I assume this is something you have to do a lot of so you can use this formula:
=LET(t,A1:K4, c,COLUMNS(t)-1, s,SEQUENCE(ROWS(t)*c,,0), titles,IF(MOD(s,c),"",INDEX(t,INT(s/c)+1,1)), data,INDEX(t,INT(s/c)+1,MOD(s,c)+2), CHOOSE({1,2},titles,data) )where t is the table of data to convert. So change A1:K4 to A3:K5 in your sample image assuming the upper left corner with the text "How Data is" is in cell A1
mtarler You are correct I have lot of data to do so hence need something which will make it easy.
I tried formula suggested however something is not correct getting some error, snapshot attached.
- do you have latest version of Excel? Try typing LET and see if Excel recognizes it.
