Copying data from 205 columns to 1 column

Hi,

I am using Excel 2007 and have a table that has 205 columns and 15 rows. I
want to copy the data of the 15 rows of every column under Column A with a
blank row between the data of every column.

Example:

A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3

B1
B2
B3

C1
C2
C3

I appreciate if somebody can help with this. If this question has been
posted before, please direct me to the post.

Thanks,
Zuo

Assume your source data as described is in Sheet1, in A1:A15 across by 205
cols ie 15 rows x 205 cols
In another sheet,
Put this in any startcell, say in B2:
=IF(MOD(ROWS($1:1)-1,16)=15,"",OFFSET(Sheet1!$A$1,MOD(ROWS($1:1)-1,16),INT((ROWS($1:1)-1)/16)))
Copy B2 down as far as required to exhaust the source data exactly as
desired. voila? eternalize it, hit YES below
--
Max
Singapore
---
"Zuo" wrote:
I am using Excel 2007 and have a table that has 205 columns and 15 rows. I
want to copy the data of the 15 rows of every column under Column A with a
blank row between the data of every column.

Example:

A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3

B1
B2
B3

C1
C2
C3

I appreciate if somebody can help with this. If this question has been
posted before, please direct me to the post.

Thanks,
Zuo

Max,

Thank you for your prompt reply. Your understanding of the source data set
up is correct. The data (15 rows x 205 columns) is sitting in Sheet 1 of the
file “Data Source�, then I copied your formula in cell B2 of sheet 2 of the
same file. As I paste the formula a “0� appears on the cell but then it
prompts me to “Update Values: Sheet 1� took me to the location of the file
in my computer, I selected the file “Data Source�, then it prompts me again
to “select the sheet to update values from:� and it gives me the option of
sheet 1 or sheet 2. As I select either it gives me a #value error. Please
advise.

Best Regards,

Zuo

"Max" wrote:

Assume your source data as described is in Sheet1, in A1:A15 across by 205
cols ie 15 rows x 205 cols
In another sheet,
Put this in any startcell, say in B2:
=IF(MOD(ROWS($1:1)-1,16)=15,"",OFFSET(Sheet1!$A$1,MOD(ROWS($1:1)-1,16),INT((ROWS($1:1)-1)/16)))
Copy B2 down as far as required to exhaust the source data exactly as
desired. voila? eternalize it, hit YES below
--
Max
Singapore
---
"Zuo" wrote:
I am using Excel 2007 and have a table that has 205 columns and 15 rows. I
want to copy the data of the 15 rows of every column under Column A with a
blank row between the data of every column.

Example:

A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3

B1
B2
B3

C1
C2
C3

I appreciate if somebody can help with this. If this question has been
posted before, please direct me to the post.

Thanks,
Zuo

Max,

I manage to fix the formula. It had to do with the sheet 1 portion of the
OFFSET function. I deleted the sheet 1 and moved the formula to the same
sheet where the data is located. Thank you very much for your help.

"Zuo" wrote:

Max,

Thank you for your prompt reply. Your understanding of the source data set
up is correct. The data (15 rows x 205 columns) is sitting in Sheet 1 of the
file “Data Source�, then I copied your formula in cell B2 of sheet 2 of the
same file. As I paste the formula a “0� appears on the cell but then it
prompts me to “Update Values: Sheet 1� took me to the location of the file
in my computer, I selected the file “Data Source�, then it prompts me again
to “select the sheet to update values from:� and it gives me the option of
sheet 1 or sheet 2. As I select either it gives me a #value error. Please
advise.

Best Regards,

Zuo

"Max" wrote:

Assume your source data as described is in Sheet1, in A1:A15 across by 205
cols ie 15 rows x 205 cols
In another sheet,
Put this in any startcell, say in B2:
=IF(MOD(ROWS($1:1)-1,16)=15,"",OFFSET(Sheet1!$A$1,MOD(ROWS($1:1)-1,16),INT((ROWS($1:1)-1)/16)))
Copy B2 down as far as required to exhaust the source data exactly as
desired. voila? eternalize it, hit YES below
--
Max
Singapore
---
"Zuo" wrote:
I am using Excel 2007 and have a table that has 205 columns and 15 rows. I
want to copy the data of the 15 rows of every column under Column A with a
blank row between the data of every column.

Example:

A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3

B1
B2
B3

C1
C2
C3

I appreciate if somebody can help with this. If this question has been
posted before, please direct me to the post.

Thanks,
Zuo

"Sheet1" is the assumed name of your source data sheet, as mentioned in my
response. Anyway, glad you got it up n working. Do take a moment to hit the
YES button in the earlier response though ...
--
Max
Singapore
---
"Zuo" wrote:
Max,

I manage to fix the formula. It had to do with the sheet 1 portion of the
OFFSET function. I deleted the sheet 1 and moved the formula to the same
sheet where the data is located. Thank you very much for your help.