Row to column list

vignesh_thegame

New Member
Joined
Sep 30, 2013
Messages
48
Hi There,

I need your help to convert the rows into column:

Lesson NameLO 1LO 2LO 3LO 4
FruitAppleMangoWatermelon
VegetablesCarrotBeetroot
FlowersRoseJasmineTulipDhalia
OutputAny output like below format is fine.
Lesson NameLO sLesson NameLO s
FruitAppleFruit
MangoApple
WatermelonMango
VegetablesCarrotWatermelon
BeetrootVegetables
FlowersRoseCarrot
JasmineBeetroot
TulipFlowers
DhaliaRose
Jasmine
Tulip
Dhalia
 

Attachments

  • List.PNG
    List.PNG
    21.3 KB · Views: 5

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
you can try Power Query to get PQ Table or create Pivot Table from PQ Table

pqpt.png

M for PQ Table:
Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Lesson Name"}, "Attribute", "Value"),
    RC = Table.RemoveColumns(Unpivot,{"Attribute"})
in
    RC
 
Upvote 0
you can try Power Query to get PQ Table or create Pivot Table from PQ Table

View attachment 17826
M for PQ Table:
Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Lesson Name"}, "Attribute", "Value"),
    RC = Table.RemoveColumns(Unpivot,{"Attribute"})
in
    RC
Thanks for the response, Can you guide me how to make PQ table, i selected the date and run pivot query table looks like this. its was not stacked like your screenshot,

P.S: i have updated date in below sheet,
 
Upvote 0
1. update your profile about Excel version because I am not sure you can use Power Query
2. I your Excel contain Power Query just select source table then from Data tab find and select From Table and in new Power Query Editor window select first column then find Unpivot columns and choose Unpivot other columns then Close&Load
 
Upvote 0
Ok i did as per your guidance and got the screenshot 1 (PQ table) , but im getting a new column "Arrtibute.1" how to remove this, also after this i did closed and load, but where to run the query

1594212845115.png
 
Upvote 0
if you want Pivot Table do this

connect.png

then Insert - Pivot Table - Use an external data source ....
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top