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.
 
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 A1:D3 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 A1:D3 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.
 

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