Forum Discussion
help with separating rows into columns
Haytham Amairah , Hi Haytham, thanks, see attached file. Not sure where or how to save a working copy of a spreadsheet in this community....
Does each column always start with (Last, First) and end with the same email?
However, you can attach the file using this little button:
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 :=) )
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:
- Formulas & Functions
- VBA code
- Dynamic Arrays
- 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
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,
