help with separating rows into columns

[ccorey05]

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

[#2]

Hi,

You can use the Transpose feature.

Please check out this link and this to learn more about it.

Hope that helps

[#3]

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 advance

[#4]

@ccorey05 

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:

1. Do you want it to be dynamic or static (does not refresh when source data changes)
2. Do you have Office 365 Insider (you can then use the TRANSPOSE function without CTRL+ SHIFT + ENTER
3. Do you like using Power Query? it's easy to refresh
4. 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:

1. Paste Special >>Transpose
2. Shortcuts
3. Transpose Function with CTRL+SHIFT+Enter
4. Transpose Dynamic Array Function (only Office 365 Insider)
5. Index, Columns & Rows Functions
6. CHAR & INDIRECT functions
7. Find & Replace method
8. Power Query (can be automatically Refreshed)

Let me know which technique you like most.

Best of luck with your project

Nabil Mourad

[#5]

@excel_geek

Hi,

This might be automated using VBA or Power Query!

But I need the workbook or a sample of it to look into that!

[#6]

@ccorey05 

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

[#7]

@Haytham Amairah , Hi Haytham, thanks, see attached file. Not sure where or how to save a working copy of a spreadsheet in this community....

[#8]

@excel_geek

Does each column always start with (Last, First) and end with the same email?

However, you can attach the file using this little button:

[#9]

@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

[#10]

@Haytham Amairah - Hi Haytham, unfortunately, they don't all start with (Last, First) and end with the same email. Some of the 7 rows datasets don't have first/last name, but they have 7 rows.

The only consistency is that when the 4 column dataset is used it's row/column has personal data comingled. And, the other datasets are 7 row. Of these some have a first, last name others have a blank line.

I'm not sure how to separate the 4 row datasets. I can identify the first row of that 4 row sequence using this wild card: *MyCampaign ID*Preferred Phone*

See attached example (thanks for the hint :=) )

[#11]

@excel_geek 

hello,

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:

1. You need to create a Defined Name using an Offset Function
2. 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

[#12]

@excel_geek 

Hi again

The sample you attached requires a different solution other than what I explained in previous posts.

This is called Unstacking records. I can solve it in 4 different methods:

1. Formulas & Functions
2. VBA code
3. Dynamic Arrays
4. Power Query

Here is a link to my video tutorial for a similar situation where I explain the 4 methods with a sample file to download

https://www.youtube.com/watch?v=LzEGcRWEmRw

Good Luck

Nabil Mourad

[#13]

Thanks so much for the responses, I'll look into it,

[#14]

@nabilmourad 

Excellent video, nicely done and thank you for pointing me there. However, my data is unevenly stacked. Do you have a series of video for that?

The index function requires a fixed length array, in your case 10 rows. = My case this is a variable of either 4 or 7 rows, depending on the data.

In your VBA script example, the same limitation of 10 rows in the range.  In my case, can I use a text search to change the fixrange variable from 7 to 4 rows in the range? If the row contains  *MyCampaign = ID*Preferred Phone , the variable is 4 otherwise variable is 7.  This seems to affect the step attribute of the loopcounter too. Can the loop be set to a variable depending on the number of dynamic rows in the data?

In Power query example, same issue using Modulo to create the index, can this be a dynamic variable based on data in the stack? A 4 or 7 row data-set to transpose in a ~15,000 row file.

Dynamic Arrays has the same fixed row limitation, I need a dynamically assigned row count based on data. In my case there are 2 conditions, a 7 = row dataset, and a 4 row dataset from a single worksheet.

Thanks,

[#15]

@excel_geek 

Conclusion

There should be any pattern in your data to use any of the solutions.

If the data is totally random then (to my opinion) it can't be unstacked.

[#16]

@nabilmourad 

A VB solution was provided by another excel community member. Thanks,