Copying data from 205 columns to 1 column


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
 
Ad

Advertisements

M

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
 
Z

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
 
Z

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

Advertisements

M

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top