VBA To Separate One Long Row Data Into Multiple Rows Of Fixed Columns

[rxpsych]

Good afternoon,

I copied and pasted a quiz I am set to give on Wednesday (in 2 days) into Excel and it is appearing as one very long row of cells. The question appears in the first cell and then each multiple choice answer (A, B, C, D, etc) are each in a separate cell after. Then it repeats for 50 questions. I would cut and paste them into different rows if it weren't so many questions. So, I have one row with cells A1 to MY1. Each question represents 6 cells in a row (Question + 5 possible answers). I just need to insert a new column after each 6th cell. I've searched the web and many forums and thought I'd find a simple answer to my dilemma, but cannot. I know nothing about writing VBA code, but I know where and how to input it into a macro. Can anyone assist me in task? Thank you in advance.


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ)
https://www.youtube.com/watch?v=jdPeMPT98QU (https://www.youtube.com/watch?v=jdPeMPT98QU)
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg (https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg.9fxrOrrvTln9g9wr8mv2 CS (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg.9fxrOrrvTln9g9wr8mv2 CS)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g96yGbAX 4t (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g96yGbAX 4t)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g7lhoX-ar5 (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g7lhoX-ar5)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl )
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg.9g9wJCunNRa9gJGhDZ4R I2 (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg.9g9wJCunNRa9gJGhDZ4R I2)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Excel Fox]

Can you attach the file on to a file share site like 4share and post the link here? If the questions are confidential, you can create a sample that represents your original file.

[rxpsych]

Thank you for your assistance. Here is a link to my file.... https://www.dropbox.com/s/co1xapy9z42yksu/Book1.xlsx

[Excel Fox]

Use this in a module


Sub InsertColumnsAtN()

Dim lngCol As Long

For lngCol = 289 To 7 Step -6
ActiveSheet.Columns(lngCol).Insert
Next lngCol

End Sub

[rxpsych]

Unfortunately, it didn't do anything but blink and inserted a whole lot of empty columns. I may have explained what I needed incorrectly. I wanted all of my questions to appear in column A, and each answer to appear in columns B,C,D,E, and F, respectively. Sorry for the miscommunication. I need it this way to import it into some new testing software that I'm trying out. Thank you.

[Excel Fox]

I just need to insert a new column after each 6th cell.Clearly, that doesn't reflect in your statement here!!

EDIT: So if you expect a solution, I'd suggest you post a sample of the expected output. I will then provide a solution as it deems fit

[snb]

Sub M_snb()
sn=sheets(1).cells(1).currentregion
redim sp( ubound(sn,2)\5,4)

for j=1 to ubound(sn,2)
sp((j\5,j mod 5)=sn(1,j)
next
cells(10,1).resize(ubound(sp)+1,ubound(sp,2)+1)=sp
End Sub

[rxpsych]

Excel Fox, I sincerely apologize for my miscommunication. I should have said a "return" after the sixth cell, as happens in a Word document to go to the next line down. I know Excel is not Word and the lingo is different, so please forgive my ignorance.

snb... Thank you for the suggested code, but it throws a Syntax error in the line "sp((j\5,j mod 5)=sn(1,j)"

Thank you both for your help, but since time is growing short, I've saved the Excel sheet as a CSV and opened it in Notepad. I then just hit ENTER after the sixth comma and saved it. Then I opened it back up in Excel and it did what I needed it to do. Sometimes the manual way takes less time than trying to figure out the easy way.

I do truly appreciate your willingness to help. I'd still love to figure it out at some point because I'm sure it's going to become an issue again. Just so I can be more clear, I have one long row where in each cell there is: Q1, 1A, 1B, 1C, 1D, 1E, Q2, 2A, 2B, 2C, 2D, 2E, Q3, 3A, 3B, etc. up to Q50 with A to E multiple choice answers for each. I need to have 50 rows where all the Questions (Q1 to Q50) are in one column, all A answers are in the next column, all B answers in the next column, etc, up to E answers. That is why I saved as a CSV and just hit enter after every "E" answer. It put the next question on the next row, but I'm sure there's a VBA code to do it easier. Thanks.

[snb]

Use:


Sub M_snb()
For j = 1 To Cells(1, Columns.Count).End(xlToLeft).Column Step 6
Cells(j \ 6 + 2, 1).Resize(, 6) = Cells(1, j).Resize(, 6).Value
Next
End Sub

[rxpsych]

Thank you snb. That worked perfectly. Everything copied below the first row exactly as it needed to be.

[arunreddy2004]

I have a similar problem as rxpsych even bigger as i have multiple such columns of patients data . But i dont know how to apply the code given here. can somebody explain the method of applying the code. Screenshots will be more helpful. Thanks in advance.