Jul 08 2021 05:33 AM
Hi All,
Can you suggest how I can move data from multiple columns to rows below one another.
below is example how i need
Jul 08 2021 06:32 AM - edited Jul 08 2021 06:36 AM
@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
Jul 08 2021 06:47 AM
Alternatively with Power Query. Sample attached
Jul 08 2021 06:49 AM
@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.
Jul 08 2021 06:58 AM
Jul 08 2021 07:07 AM
Jul 08 2021 07:11 AM
@L z. not much familiar with query, can you guide :)
Jul 08 2021 07:13 AM
Jul 08 2021 07:25 AM
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
Jul 08 2021 07:26 AM
SolutionHere 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.
Jul 13 2021 02:56 AM
Jul 13 2021 04:51 AM
@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
Jul 13 2021 05:20 AM
@mtarler what needs to do if I want output like
Jul 13 2021 06:41 AM
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"
Jul 13 2021 07:33 AM
@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.
Jul 08 2021 07:26 AM
SolutionHere 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.