Parse Functions‎ > ‎

Column to Columns

                                                                                             Sample File: ColumnToColumns.xls

PART 1 - Data separated by blank cells 

PROBLEM:  
 
"I have a single column of data that contains multiple data sets of varying lengths. Each data set is separated by a space.
I need each group of data in a column of its own."
 
 
EXAMPLE
                           BEFORE
 
  A B C D
1 0.35      
2 0.33      
3 0.21      
4        
5 0.2      
6 0.16      
7 1.61      
8 1.2      
9 0.08      
10 0.51      
11        
12 0.37      
13 0.56      
14 0.7      
15 0.77      
16 0.85      
17 0.67      
18        
19 0.77      
20 0.85      
21 0.67      
 
 
                                       AFTER
 
 ABCDE
10.350.20.370.77 
20.330.160.560.85 
30.211.610.70.67 
4 1.20.77  
5 0.080.85  
6 0.510.67  
7     
8     
9     
 
SPECIFICATIONS: 
  1. Data all in column A
  2. Data is flat values, not formulas
  3. Data is separated by at least one blank cell per group

CODE

Sub ColumnToColumns()
'Jerry Beaucaire, 2/7/2012
'Groups of data in column A separated by blank cell(s)
'are moved into individual columns
Dim i As Long, RNG As Range
Application.ScreenUpdating = False
Set RNG = Columns("A:A").SpecialCells(xlCellTypeConstants)
For i = 2 To RNG.Areas.Count
RNG.Areas(i).Cut Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
Next i
Set RNG = Nothing
Application.ScreenUpdating = True
End Sub
 

PART 2 - Contiguous Data

PROBLEM:
 
"I have a single column of data that needs to be parsed out to multiple columns for ease of examination.  I need a macro to split the one column into multiple columns by the number of cells I designate each time it is run."
 
EXAMPLE
                                BEFORE
 
ABCD
1John
2Sam
3Phil
4           Bob
5Doug
6Porter
7Reynold
8Phil
9Eric
10Paul
11       Randy
12Thomas
13Gerald
14Stan
15Scott
16Tom
17Michael
18         Roger
19Josh
20Matt
21Gary
 
 
                                AFTER
 
ABCD
1John John Phil Scott
2Sam Sam Eric Tom
3Phil Phil Paul Michael
4 Bob Bob Randy Roger
5Doug Doug Thomas Josh
6Porter Porter Gerald Matt
7ReynoldReynold Stan Gary
8Phil
9Eric
10Paul
11 Randy
12Thomas
13Gerald
14Stan
15Scott
16Tom
17Michael
18 Roger
19Josh
20Matt
21Gary
SPECIFICATIONS:
  1. Data all in column A
  2. Data is flat values, not formulas
  3. Macro will ask how many cells to split out to each new column 

CODE

Sub ColumnToColumns()
'Jerry Beaucaire, 5/16/2012
'Take a column of values and split into multiple columns by X cells
Dim X As Long, RNG As Range, Grp As Long

X = Application.InputBox("How many cells in each group?", "Cells per group", 48, Type:=1)
If X = 0 Then Exit Sub

Set RNG = Range("A1", Range("A" & Rows.Count).End(xlUp))

For Grp = 1 To RNG.Rows.Count Step X
    RNG.Cells(Grp).Resize(X).Copy Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
Next Grp

End Sub

 
Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online! 
 
Comments