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
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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"yudi_lks" wrote:

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

Hi Max
Wouldn't the TRANSPOSE array be easier.

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

Regards
Michael

"Max" wrote:

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"yudi_lks" wrote:

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

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" wrote in message
...
Hi Max
Wouldn't the TRANSPOSE array be easier.

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

Regards
Michael

"Max" wrote:

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"yudi_lks" wrote:

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

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

"Peo Sjoblom" wrote:

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" wrote in message
...
Hi Max
Wouldn't the TRANSPOSE array be easier.

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

Regards
Michael

"Max" wrote:

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"yudi_lks" wrote:

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

"Michael" wrote
...
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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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

Regards,
Yudi

"Max" wrote:

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"yudi_lks" wrote:

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

You're welcome !
Glad it helped ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Yudi Setiawan" <Yudi wrote in message
...
OMG.. You are simply THE BEST man!!!.. Thanks for your tips...

Regards,
Yudi

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

Yudi

"Max" wrote:

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"yudi_lks" wrote:

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

Yes, just change the number "4" in
both the INT(..) and MOD(...) parts to suit
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Yudi Setiawan" wrote in message
...
One more thing, so Max if I have 5 columns, I just have to change 4 to 5
right? Thanks..

Yudi

Cool.. Thanks

"Max" wrote:

Yes, just change the number "4" in
both the INT(..) and MOD(...) parts to suit
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Yudi Setiawan" wrote in message
...
One more thing, so Max if I have 5 columns, I just have to change 4 to 5
right? Thanks..

Yudi