Transposing Formula

I have been trying for a few days now to get this formula right, unfortunately my obligations at work prevent me from spending to much time on it. I have tried tweaking different Index and Offset formulas I have seen people use to solve a similar problem. My problem being that often at my work I am required to put together a spreadsheet for my boss. The program I export into excel from shows the data as such.

1
2
3
4
5
6
(blank row)
7
8
9
10
11
12

The data always appears in one column separated by a blank row after every 6th row. I need the Data to look like this

1 2 3 4 5 6
7 8 9 10 11 12

and so on.

So each row contains a different piece of information that i need transposed to a different column. The only hard part and the most time consuming part of this is each time I do this for my boss there could be hundreds of rows of data in that one column. Any help would be much appreciated. If this is something that would be better placed in the Macro forum thread please let me know and I will gladly delete this post and re-post it in that thread.

Assuming your data starts in cell A1, put this formula in C1:

=INDEX($A:$A,(ROWS($1:1)-1)*7+COLUMNS($C:C))

Copy it across to H1 to get the first 6 values.

Then copy C1:H1 down as far as you need to.

Hope this helps.

Pete

Works perfectly thank you very much Pete