Solved excel- need to cut data every 12 columns & paste as new row

Microsoft Excel 2010 - complete product...
January 23, 2013 at 09:48:38
Specs: Windows 7
Hi experts!

I have 0 experience with macros but i suspect that's the best solution for what i need.

I use excel 2010 and i have a data sheet with numerical response times per cell corresponding to 500 participants (24 RTs per participant). The thing is all the info for all participants is in the same row and i need each participant's 24 response times in separate rows (so that each row contains 24 columns with response times).

Is it possible to create a macro that cuts every 24 cells and pastes them as new rows?

ex, from this:

a,a, b,b, c,c, d,d, e,e, f,f, g,g, h,h, i,i, o,o, p,p, q,q, w,w, r,r, t,t, g,g, x,x, ,n,n, l,l,m,m, j,j, d,d, s,s, z,z (imagine all this text is in the same row/line)

to this:

a,a, b,b, c,c, d,d, e,e, f,f, g,g, h,h, i,i, o,o, p,p, q,q
w,w, r,r, t,t, g,g, x,x, ,n,n, l,l,m,m, j,j, d,d, s,s, z,z


See More: excel- need to cut data every 12 columns & paste as new row

Report •

January 23, 2013 at 11:08:52

So, if I understand correctly, you have a spreadsheet with the first row only populated from A1 to A12000 (500 participants* 24 responses per participant).

If this is correct then you can try this Macro that will populate a second spreadsheet in the format you are looking for i.e. 500 rows with 24 columns populated ...

Sub SplitCols()
Application.ScreenUpdating = False

LastCol = Cells(1, Columns.Count).End(xlToLeft).Columns  
'calculates the number of rows populated - we could put a check in here to ensure it is = to 12000 or at least divisible by 24

Count = 1   
        For i = 1 To LastCol Step 24
            Sheets(1).Range(Cells(1, i), Cells(1, i + 23)).Copy
            Sheets(2).Cells(Count, 1).PasteSpecial
            Count = Count + 1

End Sub

EDIT: I just noticed that the post title is different from the post. In the title you say every 12th response and in the post you say every 24th. The solution uses every 24th response.

Report •

January 23, 2013 at 16:05:02
Hi Altek!

this macro worked great!.... but only for the first 29 cases. Then it suddenly stops for some reason. I dont think this level of specificity is causing it, but since my ignorance in macros is quite large im not taking the risk.

My data has exactly 449 participants (i initially generalizedto 500), each participant having exactly 24 cells of data (no missing data). So if we divide my giant one-row of 10776 cells (449x24), we should have 449 rows of 24 columns each.

The macros does that, but it inexplicably stops after the first 29 participants. In other words after running the macros i get 29 rows of 24 columns of data, the rest of the data is just ommited and i dont know why....

please help


Report •

January 23, 2013 at 18:39:23
✔ Best Answer
That's interesting. l can't think of a reason it would stop at 29 rows or 696 responses. Is there anything strange in the source data at around the 696th column?
Do you get any error messages?

One thing we can try is to be more literal. Try replacing the following line

For i = 1 To LastCol Step 24


For i = 1 To 10776 Step 24

run that and see what happens.

Report •

Related Solutions

January 23, 2013 at 20:15:29
That worked perfect!

Thank you so much :)


Report •

January 23, 2013 at 20:23:02
No worries although I still wonder why the first attempt didn't work. I guess it will have to remain one of those unsolved mysteries - unless someone else here might have a plausible theory.

Report •

January 24, 2013 at 12:24:26
One possible way to solve the mystery might be to use some of the troubleshooting tips found in this How To:

Adding Watches and Single-Stepping can be very powerful troubleshooting tools.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Ask Question