Split single row into multiple rows

Hey everyone, I'm a new user here and glad I found this forum. Thanks in advance for your time and help.

I'm working on a worksheet that contains a large amount of data about people. Currently, I have one row dedicated per person with their demographics and survey data in separate columns. The data is such that a few columns are dedicated to demographics (name, age, etc.) and the remaining columns contain information organized by time (favoritecolor1,time1,date1, favoritecolor2,time2,date2, favoritecolor3,time3,date3, etc.). I want to convert the data so it shows multiple rows per person, but they're separated by time. row 1 would be: "john doe, 31, favoritecolor1,time1,date1", row 2 would be "john doe, 31, favoritecolor2,time2,date2", row 3 would be "john doe, 31, favoritecolor3,time3,date3", and so on and so forth. I would do it manually but the issue is that I have about 90 columns of survey data per person, split into 30 or so different time points.

In case it's still not clear, let me explain in terms of numbers. Now, for example, I have 5 rows and 10 columns. Columns 1-4 are static (information doesn't change) while columns 5 -10 are repeated trials taken at different time points (every 2 columns=1 time point of data) so 3 different time points with three pieces of information taken at each time. I want to have 15 rows and 6 columns (3 rows per person representing the different time points of data, columns 1-4 static and columns 5-6 variable).

Any help is appreciated. I don't have any experience with VB or macros so if the answer lies therein, please explain steps required or point me in that direction with any links you may have. If my explanation is bad, please let me know and I'll post tables. Thanks!!!

Hi mbasam

In your example highlight A1:D5. Hold down Ctrl and highlight G1:H5. Copy. Go to A6 and paste.
Highlight A1:D5. Hold down Ctrl and highlight i1:j5. Copy. Go to A11 and paste.
Sort by A

If this does not work, I suggest you follw the excellent advice of FDibbins and upload a sample.

Regards
Alastair

sample.xlsx

Originally Posted by FDibbins

Hi and welcome to the forum

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Thanks, FDibbins. I'm attaching a sample file with "before" for what I have and "After" of what I would like to have. Thanks for your help!

Mbasam

Originally Posted by aydeegee

Hi mbasam

In your example highlight A1:D5. Hold down Ctrl and highlight G1:H5. Copy. Go to A6 and paste.
Highlight A1:D5. Hold down Ctrl and highlight i1:j5. Copy. Go to A11 and paste.
Sort by A

If this does not work, I suggest you follw the excellent advice of FDibbins and upload a sample.

Regards
Alastair

I would do this, except it wouldn't work for my data set (please see examples). Thanks for the help!

Hi mbasam

Sorry it does not work for you - it works for me on your data.
Any VBA solution that I would write would be based on that solution.

Regards
Alastair

Originally Posted by aydeegee

Hi mbasam

Sorry it does not work for you - it works for me on your data.
Any VBA solution that I would write would be based on that solution.

Regards
Alastair

Alastair,
Thanks so much for your help, it worked! Before, it wasn't allowing me to CTRL+copy multiple selections for some reason. I tried again and it worked. Is there a way to do this with VBA coding that is less time consuming? I am starting with 300 rows and 200 columns. If not, this is fine.

Thanks again for all your help!

Mbasam

Hi mbasam

I think that he attached should work for you.

Press Ctrl+shift+Q to run the macro

Use Alt+F8 / "Step into" to see the code.

Let me know how you get on

Regards
Alastair

Hi Alastair ,

I have a very similar problem, only instead of 3 timepoints with 3 columns of data each, I have 25 timepoints with 66 columns of data each. I don't know enough vba to tell me how do edit the macro. Could you tell me what needs to be changed? Thanks in advance.

Meira Bracha

Sorry - I'm new here. I should have read the rules more carefully. Please accept my apology.

Meira Bracha.