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

[yudi_lks]

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

 

[Max]

Assuming data below is in Sheet1, cols A to D, from row1 down

1 2 3 4
5 6 7 8

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

 

[Michael]

Hi Max
Wouldn't the TRANSPOSE array be easier.

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

Regards
Michael

 

[Peo Sjoblom]

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)

 

[Michael]

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

 

[Max]

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

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

 

[Yudi Setiawan]

OMG.. You are simply THE BEST man!!!.. Thanks for your tips...

Regards,
Yudi

 

[Yudi Setiawan]

One more thing, so Max if I have 5 columns, I just have to change 4 to 5
right? Thanks..

Yudi

 

[Max]

Yes, just change the number "4" in
both the INT(..) and MOD(...) parts to suit

 

[Max]

You're welcome !
Glad it helped ..

 

[Yudi Setiawan]

Cool.. Thanks

Yes, just change the number "4" in
both the INT(..) and MOD(...) parts to suit