How to Transpose Column into Rows

[carlton7372]

Hello Community,

Can someone please show me how to transpose a column of cells in rows.

In the attached csv you will see column A has the following cells, Company, Sector, industry, continent, country, marketvalue, sales, profits, assests, rank and forbeswebpage. These cells I would like transposed (if that is the right word to use) into a their own fields / columns as shown in cols D to N. I would then like the corresponding fields filled in as shown from D2 to N2, D3 to N3, and D4 to N4.

Can you help me with that?

[AliGW]

Which version of Excel are you using?

[DavidBowman]

I assume you have already figured out that you can copy every 11 rows and then use past special to transpose them as you it looks in the attachment. The actual best way to do this is with a macro but since you have not posted in that section I would recommend you make a formula that combines all of the rows in the last 11. You can than copy that down to a long list. Then copy to values. Take the values column and use the text to columns button on the data menu to turn them to columns. Then sort them by the last column and delete the blanks in the last column and you have your data.

[carlton7372]

Hi I'm using MS Excel 2016

[mikeava]

Just copy all the cells in Column A and wherever you want the list to start (ie, D1) right click and do Paste Special - then Paste Special Again and Paste Special Box appears. Select Transpose then OK.

[carlton7372]

Hi David,

That's not exactly what I'm looking for.

But thanks for trying

[carlton7372]

mikeava,

I was hoping there was a way to achieve this with a formula or macros?

[AliGW]

Please update your forum profile, which says Excel 2010.

With Excel 2016, you can use PowerQuery for this: https://excelgorilla.com/power-bi/po...-using-modulo/

[carlton7372]

Hi Ali,

I tried the PowerQuery, it was too difficult. I have never used PowerQuery before.

[carlton7372]

Any other suggestions please ...

[AliGW]

It’s not too difficult - just follow the steps. You haven’t had time to work through them since I posted!

It’s almost identical to your own situation. PowerQuery is a fantastic tool that everyone should have in their Toolbox.

I’m on my iPad now, so can’t do a demo, but can do so in the morning if it’s still of interest. Bedtime here!

[AliGW]

You still haven’t updated your forum profile.

[carlton7372]

Hi Ali,

You're going to bed early - especially as you don't have to go into the office tomorrow - assuming you're in England, like me :-).

If you do have time tonight to help out, I'll be up quite late - if not sleep tight

Hopefully you'll help me in the morning.

[carlton7372]

mikeava,

If I would follow through with your suggestion if I just had a few rows to copy and paste. However, I have about 90,000 rows - hence why I need a formula

[AliGW]

Can’t help tonight, as I have already logged off of the laptop. You assume right that I’m not going to the office tomorrow, well not away from home, anyway, but I shall be at my desk delivering a full timetable of lessons to my pupils via MS Teams - so there!

I’ll do a PQ demo fir you in the morning over coffee.

[carlton7372]

Thanks Ali,

I look forward to it.

[AliGW]

In the meantime, for the fourth time, please update your forum profile.

[AliGW]

Please update your forum profile from Excel 2010 to Excel 2016.

Here's the M Code needed to do this with PowerQuery by following the instructions given in the link I provided:

Please Login or Register  to view this content.

At the MODULO stage, enter 11 instead of 5.

Add one step at the end: TRANSFORM tab | Use First Row as Headers.

If you wanted you could set PQ to import the data from a different workbook.

[carlton7372]

Hi Ali,

Where is my manners???

So sorry for the delay in thanking you.

I learned something new with PowerQuery

Thanks

Carlton

[carlton7372]

Please update your forum profile from Excel 2010 to Excel 2016.

Here's the M Code needed to do this with PowerQuery by following the instructions given in the link I provided:

Please Login or Register  to view this content.

At the MODULO stage, enter 11 instead of 5.

Add one step at the end: TRANSFORM tab | Use First Row as Headers.

If you wanted you could set PQ to import the data from a different workbook.

Hi Ali, I'm not sure how to reward you or close this request.

[AliGW]

Always a pleasure to open Pandora’s Box for someone!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[AliGW]

... and PLEASE update your forum profile!!!

[carlton7372]

Hi Ali,

I've marked the thread solved

I think I'm being a numbty, but I can't see the smiley face at the bottom left

Also, I've updated my profile... :-)

I've added to your reputation though

[AliGW]

No, you haven’t updated your profile - it still says Excel 2010.

Thanks for the rep.