# Multiple rows to one column

G

#### Gabriel

I have the next array with thousand of rows(C= Column, R=
Row)

C1 C2 C3 C4
R1 V1 V2 V3 V4
R2 V5 V6 V7 V8
R3 V9 VX VY VZ

I want to move or copy the values to one single column

C1
R1 V1
R2 V2
R3 V3
R4 V4
R5 V5
R6 V6
etc...
Must be simple but I have not idea ...Thanks

S

#### Steve B

I want to do just the reverse of what you are doing. I
have 3 columns with 3500 rows and I want to automate the
process of moving the data into columns across and 20
rows down then repeat until all the data has been
rearranged.

A

#### Alan Beban

What does "columns across and 20 rows down then repeat" mean?

Perhaps a small illustration (e.g., 3 columns, 10 rows) to illustrate
the kind of result you're seeking.

Alan Beban

H

#### Harlan Grove

I have the next array with thousand of rows(C= Column, R=
Row)

C1 C2 C3 C4
R1 V1 V2 V3 V4
R2 V5 V6 V7 V8
R3 V9 VX VY VZ

I want to move or copy the values to one single column

C1
R1 V1
R2 V2
R3 V3
R4 V4
R5 V5
R6 V6
etc...
Must be simple but I have not idea ...Thanks

If the original data were in A13 in one worksheet, and you wanted the topmost
result cell (V1) in cell X99, try the following in X99

=INDEX(\$A\$1:\$D\$3,INT((ROW()-ROW(\$X\$99))/COLUMNS(\$A\$1:\$D\$3))+1,
MOD(ROW()-ROW(\$X\$99),COLUMNS(\$A\$1:\$D\$3))+1)

Select X99 and fill down as far as needed. The formula returns #REF! errors once
you've exhausted the values in the original range.

G

#### Gabriel

Thanks Harlan. It works excellent.....
-----Original Message-----
...

If the original data were in A13 in one worksheet, and you wanted the topmost
result cell (V1) in cell X99, try the following in X99

=INDEX(\$A\$1:\$D\$3,INT((ROW()-ROW(\$X\$99))/COLUMNS (\$A\$1:\$D\$3))+1,
MOD(ROW()-ROW(\$X\$99),COLUMNS(\$A\$1:\$D\$3))+1)

Select X99 and fill down as far as needed. The formula returns #REF! errors once
you've exhausted the values in the original range.