Excel VBA help transposing data

Question

I am working with a dataset in excel that is organized like this with 60 more time intervals:

(Time 0)

     1 2 3 4 5 -> 12

 A   0 0 0 0 0
 B   0 0 0 0 0
 C   0 0 0 0 0 
 D   0 0 0 0 0 
 ...
 H   0 0 0 0 0

I want the output to be organized in this format were column 2 is under column 1, column 3 is under 2 etc. So it would look something like this:

Time 0 Time 10
A1 0
B1 0
C1 0
D1 0
E1 0
F1 0
G1 0
H1 0
A2 0
B2 0
C2 0
D2 0

The next column would take the data from the Time 10 interval and so on so forth. There are 61 time intervals hence the need for some sort of macro that would enable this relatively quickly.

Currently I have just set my macro to select, copy, select new area, paste but I know there has to be a faster way to do this. Any help would be appreciated!

No I don't need anyone to write my code, I was just hoping people would point me in the right direction with regards to functions or any tips on how to go about tackling this. — user3794693, Jul 01 '14 at 17:18
Just have a loop that references cell ranges and does stuff. — Tanner, Jul 01 '14 at 17:40

score 0 · Answer 1 · edited May 23 '17 at 11:57

0

VBA is not necessary. Add a character in the top left corner and create a PivotTable with multiple consolidation ranges (eg see). Drill down on the Grand Total intercept (double click), select the table and Table, Convert to Range then sort by Column within Row and add a column with =A1&B1 if desired, copy sheet and Paste Special over the top and delete Row and Column columns.

edited May 23 '17 at 11:57

Community

1
1

answered Jul 01 '14 at 19:34

pnuts

58,317
11
87
139

Excel VBA help transposing data

1 Answers1