Forum Discussion

Sumit_Bhokare's avatar
Sumit_Bhokare
Brass Contributor
Jul 08, 2021
Solved

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 need  
excel
  • mtarler's avatar
    mtarler
    Jul 08, 2021

    Sumit_Bhokare 

    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.

Sumit_Bhokare's avatar
Sumit_Bhokare
Brass Contributor
Jul 08, 2021
I have large data in similar form
mtarler's avatar
mtarler
Silver Contributor
Jul 08, 2021

Sumit_Bhokare 

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.

Resources