Put All Columns One Under the Other

[c a g a t a y]

Hi all
I did not know how to write the title truely
Simply i want try to describe what i want to do;

As i attached my excell file
i wanna put all columns one under other automatically (as you see in Sheet1)
(i did it bu manually in Sheet2)

How can i do this automatically (by formulas)

Best regards

[alansidman]

Power Query is available as an add in for Excel 2010. Once loaded, then bring your table into the PQ editor.
Highlight the first column and Unpivot the remaining columns.
Delete the two uneeded columns.

Here is the Mcode

Please Login or Register  to view this content.

[alansidman]

crosspost: https://www.mrexcel.com/board/thread...other.1146941/

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future

[c a g a t a y]

hi Alan
thanks a lot for ur answer and for your work
it is ok that u did what i want
but; when i change/add column and row datas, nothing changed? not syncing with sheet3?
is it my problem? please forgive this newbie :/

[alansidman]

If you make changes to the original source data, you need to click on Refresh All to update the output.

[c a g a t a y]

ok i find it (REFRESH ALL)
and not worked
please look orders (from 1 to 12) from Sheet1 to Sheet2 in the attached xls

thnxs

[alansidman]

I don't understand your issue. I get the same results as you when I unpivot the table. What is it that you are looking for that is different from the original example.

[c a g a t a y]

gotcha....
ur query ordering from left row to right
(as we look ur excell ordering from B2 to O2) (it is row based order)
what i want to do is; order from B2 to B13 (column based order)
is it clear?

look my v2 excell --> Sheet2

thnxs

[alansidman]

Understand now.

Please Login or Register  to view this content.

[c a g a t a y]

returns with this error

i am adding more rows and columns

[alansidman]

How is your actual file different from the example you have presented? Is the sample representative of your actual file?

[c a g a t a y]

yes, it is a sample of my actual file.
there a lots (hundreds) of columns in my actual file.
all columns have 10-20 rows in there.
i made a simple sample for this forum to be able to understand

[alansidman]

Open your file.
In Power Query, open a blank query, by clicking on From other sources and selecting Blank Query

Insert the code I have provided by clicking on Advanced Editor. Paste my code into the Advanced Editor. Make sure the table name you have for your table replaces my "Table1" if different.

Close and Load.

Don't know what else to tell you. It works for me.

[bebo021999]

A formula solution:

In A1:

Please Login or Register  to view this content.

Drag down

[c a g a t a y]

Thanks a lot @Alan
I am using Office 365 ProPlus and there is no POWER QUERY options in my Excel
I tried to add via https://www.excelcampus.com/install-power-query/
But not successfull

@Bebo
It worked (semi-worked for me)
If i add more columns and more row it is half working
Attached v3 xls...Any idea?

regards

[alansidman]

Your profile indicates 2010. Please update that to 365. In 365, power query is called Get and Transform and is found on the Data Tab of the Ribbon. It is called Power Query in 2010.

[c a g a t a y]

@alansidman my excel says it is OFFICE 365 ProPlus
And there is no UPDATE now
pls look attached image.

@bebo could you please try NAMEXMP.V3.xlsx‎ that i attached?

regards

[alansidman]

I am referring to your Excel Forum Profile that you set up when you joined here.

[bebo021999]

If you try to study my formula in #14, you may know to adapt the range from A2:A13 into A2:A31

Please Login or Register  to view this content.