Copying data from 205 columns to 1 column

  • Thread starter Thread starter Zuo
  • Start date Start date
Z

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
 
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,

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,

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