Copying data from 205 columns to 1 column

[Zuo]

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

 

[Max]

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

 

[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

 

[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.

 

[Max]

"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 ...