SOLVED

Excel data split from multiple column to row

Brass Contributor

Hi All,

Can you suggest how I can move data from multiple columns to rows below one another.

below is example how i needsample requirementsample requirement

 

16 Replies

@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

@Sumit_Bhokare 

Alternatively with Power Query. Sample attached

@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.Screenshot1.jpg

do you have latest version of Excel? Try typing LET and see if Excel recognizes it.
I think mine version is not latest, its version 2008 & excel is not recognizing LET :(

@L z. not much familiar with query, can you guide :)

I have large data in similar form

@Sumit_Bhokare 

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 as a Table

- 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

best response confirmed by Sumit_Bhokare (Brass Contributor)
Solution

@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.

Thank you!
Thanks for guide it works.
You're welcome. You said "I have lot of data" and finally seem to have opted for a formula solution
Not quite sure what "lot of data" means for you. Anyway, your call
Nice day & Thanks for providing feedback

@L z.  as I said not much use too with PQ.

I tried as per your guide & it's really working well now, thanks again.

 

I just need additional info about PQ, if possible will you help?

what needs to do if I want output like

Capture2.JPG

@mtarler what needs to do if I want output likeCapture2.JPG

@Sumit_Bhokare 

Not quite sure this is doable with the Power Query user interface only or it's going to be complex

 

See attached file with a possible option where I decomposed the steps with hopefully meaningful names

You'll see some steps are very similar. This means the query can we written differently. That would make sense if we were to deal with more than 2 sets of columns (Code, Price) but as you're new to this I kept things "simple"

@Sumit_Bhokare If you can understand and use PQ as @L z. is helping you with, that is a great skill to have and very powerful and useful.  If you want to modify the equations you just need to modify the ranges they point to.  In attached I change the range for the 1st column to only include the "Code" section to determine the # of rows needed and then the Code and Price columns only point at those regions.

1 best response

Accepted Solutions
best response confirmed by Sumit_Bhokare (Brass Contributor)
Solution

@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.

View solution in original post