Create multiple rows from a single row of data

[jojotherider]

Hi Folks,

I've been searching on here as well as various search engines and I can't find what I'm looking for so I'm making a new thread.

I have a spreadsheet that has multiple rows of data. However, I need to break up each row, because at the end, there are columns that need to be split up over different rows. the hitch is that in each row, columns A:R need to stay with the columns S and beyond. Even worse, the number of columns after R is not consistent. There can be 4 columns or there can be 20 columns. The columns are grouped by 4 and the last column in each set is a timestamp. soooo, I've attached a sample file, but I'll try to make a visual so you don't have to open the file.

colA colB colC colD colE ... ColR ColS colT colU colV colW colX colY colZ
txt1 txt2 txt3 txt4 ID 1 ... int1 var1 var1 var1 var1 var2 var2 var2 var2
txt1 txt2 txt3 txt4 ID 1 ... int2 var1 var1 var1 var1 var2 var2 var2 var2
txt1 txt2 txt3 txt4 ID 2 ... int1 var1 var1 var1 var1
txt1 txt2 txt3 txt4 ID 3 ... int1

Hopefully that paints a helpful picture. In colE you can see that there are 3 different IDs. ID 1, has two different INTs in colR and different variables across the remaining columns. What I'm looking to do is create a second line for those extra variables in colW-colZ. Hopefully that makes sense

The end result would look like this:

colA colB colC colD colE ... ColR ColS colT colU colV
txt1 txt2 txt3 txt4 ID 1 ... int1 var1 var1 var1 var1
txt1 txt2 txt3 txt4 ID 1 ... int1 var2 var2 var2 var2
txt1 txt2 txt3 txt4 ID 1 ... int2 var1 var1 var1 var1
txt1 txt2 txt3 txt4 ID 1 ... int2 var2 var2 var2 var2
txt1 txt2 txt3 txt4 ID 2 ... int1 var1 var1 var1 var1
txt1 txt2 txt3 txt4 ID 3 ... int1

sampledata.xlsx removed, please see post #3

sample file attached. Thanks in advance for any help.



-joel

[David A Coop]

Sorry Joel - this is very confusing, and the sample file just makes it worse.

Can you show in the sample file what you want to achieve please?

Regards,

David

[jojotherider]

Thanks for looking into it David. I'm attaching a new sampledata file here. There are two tabs. One that shows the original data and the other shows the modified version that I'm looking for.

sampledata.xlsx

again I appreciate the help.

-joel

[millz]

Try running this on the Original worksheet, a new worksheet will be created.

Please Login or Register  to view this content.

[David A Coop]

Hi Joel,

The following code is my alternative to that supplied by Millz.

I post it only to show a different approach. The Millz macro is quite clever in that the data is fed into an array which will be quicker for large volumes of data. I have simply copy and pasted the data, and it flicks between the two sheets which is slower and less efficient.

Please Login or Register  to view this content.

Regards,

David

When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[jojotherider]

Thank you so much Millz! That works great! I applied it to my sampledata and it worked perfectly. I will throw it at my live data tomorrow when I get in the office.

Etiquette question - I marked the thread as solved. Should I wait until I run it against my live data rather than the sample data?

Thank you so much for the help. I really appreciate it. I have about 80 workbooks to go through and about 2k rows in each file. This will save me soooo much time.

-joel

[millz]

Hi David, screen flickering are usually caused by Selects. I am sure your approach will be just as fast without the (unnecessary) select commands. Also, a good practice will be declaring all variables that are used in your macro (I learned this the hard way too). Other than these, you have provided a good solution.


@joel
No problem for marking it as Solved, it just reduces (by a lot) the number helpers that may visit this thread, but I will still respond to this if the problem isn't exactly solved.