View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg
 
Posts: n/a
Default Splitting one column into multiple columns

Here is a non-VBA solution.
Add a helper column and row to make it look like this:

Seq Title 1 10 16 20 23 Ttab
1 A_1 A_1 B_1 C_1 D_1
2 A_2 A_2 B_2 C_2 D_2
3 A_3 A_3 B_3 C_3
4 A_4 A_4 B_4
5 A_5 A_5 B_5
6 A_6 A_6
7 A_7 A_7
8 A_8 A_8
9 Title
10 B_1
11 B_2
12 B_3
13 B_4
14 B_5
15 Title
16 C_1
17 C_2
18 C_3
19 Title
20 D_1
21 D_2

Name the numbers in the first column (1 thru 21) Seq
Name the numbers in the first row (1, 10, 16, 20, 23) TTab
Name your data MyData
The first cell of TTab =1
The last cell of TTab has the formula
=ROWS(MyData)+1
The other cells of TTab have this formula
=MATCH("Title",INDEX(MyData,Ttab C[-1]+1):
INDEX(MyData,ROWS(MyData)),0)+Ttab C[-1]
Fill the remaining 23x4 array with
=IF((Ttab+Seq)<Ttab C[1],INDEX(MyData,Ttab+Seq),"")
The above formulas are in R1C1 Ref Style.
Uncheck this option, if desired, after final data entry.