Automatically transposing
 

Hello Forum,

I am currently working with a dataset that contains 8 columns and 500
rows of numeric data. I need to convert the 8 cols and 5000 rows to
single column which contains all the records (1 column, 40000 rows).
Now I know I could do this by copying each row then past
special/transpose it to a new column row by row, however this will tak
a lot of time with 5000 rows. Does anyone know of doing this using VB
or a formula that would speed up the process?

Below is an illustration of the problem:

From:

A B C D
1 2 3 4
5 6 7 8

To:

A
1
2
3
4
5
6
7
8

Thanks in advance.

Craig

--
Message posted from http://www.ExcelForum.com

Automatically transposing
 

Hi
enter the following formula on a separate sheet in cell A1
=OFFSET('sheet1'!$A$1,INT((ROW()-1)/8),MOD(ROW()-1,8))
and copy this down

-----Original Message-----
Hello Forum,

I am currently working with a dataset that contains 8
columns and 5000
rows of numeric data. I need to convert the 8 cols and
5000 rows to a
single column which contains all the records (1 column,
40000 rows).
Now I know I could do this by copying each row then paste
special/transpose it to a new column row by row, however
this will take
a lot of time with 5000 rows. Does anyone know of doing
this using VBA
or a formula that would speed up the process?

Below is an illustration of the problem:

From:

A B C D
1 2 3 4
5 6 7 8

To:

A
1
2
3
4
5
6
7
8

Thanks in advance.

Craig.


---
Message posted from http://www.ExcelForum.com/

.