Feb 22 2019 06:29 AM
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
Feb 22 2019 06:44 AM
Sep 08 2019 10:41 PM
Sep 09 2019 12:06 AM - edited Sep 09 2019 07:58 AM
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:
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:
Let me know which technique you like most.
Best of luck with your project
Nabil Mourad
Sep 09 2019 07:18 AM
Hi,
This might be automated using VBA or Power Query!
But I need the workbook or a sample of it to look into that!
Sep 09 2019 08:04 AM
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
Sep 09 2019 08:06 AM
@Haytham Amairah , Hi Haytham, thanks, see attached file. Not sure where or how to save a working copy of a spreadsheet in this community....
Sep 09 2019 08:22 AM
Does each column always start with (Last, First) and end with the same email?
However, you can attach the file using this little button:
Sep 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
Sep 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 :=) )
Sep 09 2019 08:50 AM
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:
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
Sep 09 2019 08:59 AM
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:
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
Sep 09 2019 09:06 AM
Sep 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.
Thanks,
Sep 12 2019 05:09 PM
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.
Sep 13 2019 06:34 AM