Forum Discussion
help with separating rows into columns
How can I separate data that is in rows into different columns? Note that in the picture you can see that some clients have more contacts than others. Any tips?
Corey
15 Replies
Hi again,
If you ask me for my personal preference, I would go with method Five (Index, Columns, Rows Function) or Method Eight (Power Query)
However if you have Office 365 Insider then beyond a doubt Dynamic Arrays method Four takes 30 seconds to create.
Watch the different methods in my video tutorial here is the link another time (It's a one stop shop)
https://www.youtube.com/watch?v=tCTepTNxRqc
Good Luck
Nabil Mourad
nabilmourad - Hi Nabil, thanks for the link to your excellent videos. I'd like to use the index method, but the range of rows in the array varies from 4 to 7. dynamic arrays sounds useful, but I have Office 365 Personal, how do I add this method? In the meantime, I'll try power query, will reply later. Thanks
Hello,
There are Eight methods to switch columns into rows. before picking up the technique that works best for you out of the EIGHT methods you have to ask yourself some questions:
- Do you want it to be dynamic or static (does not refresh when source data changes)
- Do you have Office 365 Insider (you can then use the TRANSPOSE function without CTRL+ SHIFT + ENTER
- Do you like using Power Query? it's easy to refresh
- Do you prefer classic functions?
I am going to show you the EIGHT techniques and it's up to you to select the one that works best for you. I therefore created a video tutorial showing and comparing the eight methods. You can also download the sample file I used for my recording
https://www.youtube.com/watch?v=tCTepTNxRqc
The techniques I used are:
- Paste Special >>Transpose
- Shortcuts
- Transpose Function with CTRL+SHIFT+Enter
- Transpose Dynamic Array Function (only Office 365 Insider)
- Index, Columns & Rows Functions
- CHAR & INDIRECT functions
- Find & Replace method
- Power Query (can be automatically Refreshed)
Let me know which technique you like most.
Best of luck with your project
Nabil Mourad
Hi,
You can use the Transpose feature.
Please check out this https://support.office.com/en-us/article/Transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419F2E3-BEAB-4318-AAE5-D0F862209744 and https://www.excel-easy.com/examples/transpose.html to learn more about it.
Hope that helps
- Hi, sorry to restart an old thread, but I have a similar question but too many rows to use the copy, transpose method.
Is there a way to create a transpose function to use on a column (~20,000+ rows) that I need to transpose to rows?
I've tried the offset command but, some of the datasets are 4 rows, others are 7 rows.
Any ideas? Thanks in advancehello,
to answer your questions:
If you have a variable number of Columns and Rows then using the Index function will require a little bit more doing:
- You need to create a Defined Name using an Offset Function
- Use the Defined name you created for the Array argument of the INDEX function.
As for Office Insider, so long as you already have Office 365, then you can register for it at insider.office.com
Good Luck
Nabil Mourad
