Multiple rows to one column

  • Thread starter Thread starter Gabriel
  • Start date Start date
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
 
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.
 
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
 
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.
 
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.
 
Back
Top