Excel: Convert 1 column of data to several 1

[MrStohler]

Problem:
I have several Excel files, each has one column of data (approx 1500 rows) with evenly spaced blank lines after each data entry (either 2 or 3 depending on who generated the data, but it is consistent within each file).

Goal:
Organize the data into columns, alternating data rows with one blank row. It would be ideal to set a fixed number of rows and let the number of columns be determined by the amount of data.

Example:
Current data;
001


002


003


004


005


006


007


008


Objective;

001 002 003

004 005 006

007 008


Thanks for any assitance.

 

[AnotherHiggins]

Why the blank lines? Your data will be easier to parse if it is contiguous. This would also be more easily achieved.

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[MrStohler]

Blank lines will make the data compatable with blank tables already in use. But, if the re-organization could be accomplished without the blank lines, they ciuld easily be added manulally (the objective array will either have 7, 10 or 14 rows of data, so adding blanks is not too bad).

Thanks

 

[byundt]

MrStohler,
Here is a macro to do your column manipulation. Install it in a regular module sheet. You may need to change some of the values in the constants at the beginning of the sub to match your starting point, number of blank rows and number of columns.

Sub Columnize()
Dim nCols As Long, nBlank1 As Long, nBlank2 As Long, i As Long, ii As Long, j As Long, jj As Long, k As Long, _
    nRows1 As Long, nRows2 As Long
Dim rg1 As Range, rg2 As Range
Dim X As Variant, Y As Variant
nBlank1 = 2     'Number of empty rows between each piece of data in input
nBlank2 = 1     'Number of empty rows between each row of data in results
nCols = 3       'Number of columns of data in results
Set rg1 = [A1]  'Starting point for input data
Set rg2 = rg1
Set rg1 = Range(rg1, Cells(65536, rg1.Column).End(xlUp))
nRows1 = rg1.Rows.Count

X = rg1.Value
j = Int((nRows1 - 1) / nCols) + 1
nRows2 = (j - 1) * nBlank2 + j
ReDim Y(1 To nRows2, 1 To nCols)
For i = 1 To nRows1 Step nBlank1 + 1
    ii = ii + 1
    j = Int((ii - 1) / nCols) + 1    'Row number of results without blanks
    jj = (j - 1) * nBlank2 + j      'Row number of results with blanks
    k = ((ii - 1) Mod nCols) + 1     'Column number of results
    Y(jj, k) = X(i, 1)
Next
rg1.ClearContents
rg2.Resize(nRows2, nCols).Value = Y
End Sub

To install a sub or function in a regular module sheet:
1) ALT + F11 to open the VBA Editor
2) Use the Insert...Module menu item to create a blank module sheet
3) Paste the suggested code in this module sheet
4) ALT + F11 to return to the spreadsheet

To run a sub or macro:
5) ALT + F8 to open the macro window
6) Select the macro
7) Click the "Run" button

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Brad

 

[MrStohler]

Thanks Brad,

This is perfect!