02-22-2019 06:29 AM
09-08-2019 10:41 PM
09-09-2019 12:06 AM - edited 09-09-2019 07:58 AM
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:
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
The techniques I used are:
Let me know which technique you like most.
Best of luck with your project
09-09-2019 08:04 AM
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)
09-09-2019 08:36 AM
@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
09-09-2019 08:47 AM
@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 :=) )
09-09-2019 08:50 AM
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:
As for Office Insider, so long as you already have Office 365, then you can register for it at insider.office.com
09-09-2019 08:59 AM
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:
Here is a link to my video tutorial for a similar situation where I explain the 4 methods with a sample file to download
09-10-2019 12:23 PM
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.