How can I convert a data from multiple rows into 1 column?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

HI.. Is there any way to convert multiple rows data into 1 single column?


Like this
1 2 3 4
5 6 7 8

Into
1
2
3
4
5
6
7
8

The problem is I have 687 rows, which I'm sure will take forever to do it
manually..

Many thanks!!

Yudi
 
Assuming data below is in Sheet1, cols A to D, from row1 down
1 2 3 4
5 6 7 8
Click to expand...
etc

In Sheet2
-----------
Put in A1:
=OFFSET(Sheet1!$A$1,INT((ROWS($A$1:A1)-1)/4),MOD(ROWS($A$1:A1)-1,4))

Copy down until zeros appear signalling exhaution of data
 
Hi Max
Wouldn't the TRANSPOSE array be easier.

=TRANSPOSE($A$1:$D$2), with CSE

Regards
Michael
 
That would return a 2 column range

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Hi Peo
I probably should have been more specific, but I was only pointing out the
option.

I know, I know....do it right or don't do it at all

Michael
 
Michael said:
Wouldn't the TRANSPOSE array be easier.
=TRANSPOSE($A$1:$D$2), with CSE
Click to expand...

Think TRANSPOSE wouldn't be suitable here (as per Peo's response), since the
OP's intent was to strip the source grid's* contents out into a single column
...
*687 R x 4 C
 
One more thing, so Max if I have 5 columns, I just have to change 4 to 5
right? Thanks..

Yudi
 
Back
Top