Convert large dataset into 2 columns into multiple columns

needexcelhelp

New member
Joined
Jan 17, 2017
Messages
2
Reaction score
0
Points
0
Hi. I have a large dataset from multiple experiments that are all in two columns, when in reality they should be split into 60 or so columns from 30 separate experiments. The dataset is long, > 20,000 rows, so manually cutting and pasting would be onerous. But they should all be of the same length. These are numerical data so the text to column command won't work. There must be an easy way to do this. Thanks in advance!
 
Hi and welcome
Could you please post a sheet ( click Go advanced - Manage attachments) containing some sample data and expected results ?
Thanks
 
Is it pure text? I'm not very sure what your data looks like but if you have some kind of delimited, like a comma or even just space ( ), you can use Text-to-Columns under Data tab.
 
Hi and welcome
Could you please post a sheet ( click Go advanced - Manage attachments) containing some sample data and expected results ?
Thanks
Sure. Here's a small subset. In columns AB there is a long list and in G-N I've split that into XY type points with each having a "length" 158 points.
 

Attachments

  • sample.xlsx
    62.7 KB · Views: 14
Run this short macro while the relevant sheet is the active sheet. Results are in cell P1 and beyond:
Code:
Sub blah()
With Range("P1:Y158")
  .FormulaR1C1 = "=INDEX(R1C1:R634C2,(INT(COLUMN()/2)-COLUMN(R1C8))*158+ROW(),MOD(COLUMN(),2)+1)"
  .SpecialCells(xlCellTypeFormulas, 16).Clear
  .Value = .Value
End With
End Sub
 
An updated version for different numbers of rows and different 'lengths' of results (number of rows in the results):
Code:
Sub blah()
RowsToProcess = 21000  '<< adjust this value yourself (though it can be automated).
NewLength = 158  '<< adjust this value yourself.
With Range("p1").Resize(NewLength, 2 * Application.WorksheetFunction.Ceiling(RowsToProcess / NewLength, 1))
  .FormulaR1C1 = "=INDEX(R1C1:R" & RowsToProcess & "C2,(INT(COLUMN()/2)-COLUMN(R1C8))*" & NewLength & "+ROW(),MOD(COLUMN(),2)+1)"
  On Error Resume Next: .SpecialCells(xlCellTypeFormulas, 16).Clear: On Error GoTo 0  'clears the formulae which result in an error.
  .Value = .Value  'converts formulae to plain values.
End With
End Sub
If you comment-out (put an apostrophe at the beginning of the line) the following 2 lines:
On Error Resume Next: .SpecialCells(xlCellTypeFormulas, 16).Clear: On Error GoTo 0 'clears the formulae which result in an error.
.Value = .Value
'converts formulae to plain values.

it will leave the formulae in place on the sheet so you can see how it works.

In the attached is the macro and a button on the sheet to execute it.
 

Attachments

  • ExcelGuru7236sample.xlsm
    386.2 KB · Views: 9
Last edited:
Oh well, another person helped who can't be bothered to acknowledge the help he's got.
 
Back
Top