transpose multiple rows at once
 

hii all...

a small doubt about Transpose in Edit menu.

Here Iam spending much time to transpose each row into one column. There is
any option to reduce my time on this issue?
The data is like this.......

A B C
1 12 13 14
2 15 16 17
3 18 19 20

I wanna arrage the values spread in A, B, C..into one A coulmn. I use
pastespecial--transpose. But it takes much time to arrange all rows. Is
there any other option to transpose all the data into one column???
I need the data like this...

A
1 12
2 13
3 14
4 15
5 16
6 17
7 18
8 19
9 20


Thanks,
Narendra

transpose multiple rows at once
 

Try this:

A B C
1 12 13 14
2 15 16 17
3 18 19 20

Enter this formula in any cell:

=OFFSET(A$1,INT((ROWS($1:1)-1)/n),MOD(ROWS($1:1)-1,n))

n = the number of columns in the table. In your case n = 3.

Copy down until you get a contiguous return of 0 (meaning the data has been
exhausted).

With the range of formulas still selected do:

EditCopy
Then EditPaste SpecialValuesOK
Delete the cells with 0's
Delete the original table if desired

Biff

"Narendra Boga" wrote in message
...
hii all...

a small doubt about Transpose in Edit menu.

Here Iam spending much time to transpose each row into one column. There
is
any option to reduce my time on this issue?
The data is like this.......

A B C
1 12 13 14
2 15 16 17
3 18 19 20

I wanna arrage the values spread in A, B, C..into one A coulmn. I use
pastespecial--transpose. But it takes much time to arrange all rows. Is
there any other option to transpose all the data into one column???
I need the data like this...

A
1 12
2 13
3 14
4 15
5 16
6 17
7 18
8 19
9 20


Thanks,
Narendra

transpose multiple rows at once
 

=INDEX($A$1:$C$3,ROUNDUP(ROW(A1)/3,0),IF(MOD(ROW(A1),3)=0,3,MOD(ROW(A1),3)))

copy down


"Narendra Boga" wrote:

hii all...

a small doubt about Transpose in Edit menu.

Here Iam spending much time to transpose each row into one column. There is
any option to reduce my time on this issue?
The data is like this.......

A B C
1 12 13 14
2 15 16 17
3 18 19 20

I wanna arrage the values spread in A, B, C..into one A coulmn. I use
pastespecial--transpose. But it takes much time to arrange all rows. Is
there any other option to transpose all the data into one column???
I need the data like this...

A
1 12
2 13
3 14
4 15
5 16
6 17
7 18
8 19
9 20


Thanks,
Narendra

transpose multiple rows at once
 

Thank You very much. it is working. Thanq thnq

"T. Valko" wrote:

Try this:

A B C
1 12 13 14
2 15 16 17
3 18 19 20

Enter this formula in any cell:

=OFFSET(A$1,INT((ROWS($1:1)-1)/n),MOD(ROWS($1:1)-1,n))

n = the number of columns in the table. In your case n = 3.

Copy down until you get a contiguous return of 0 (meaning the data has been
exhausted).

With the range of formulas still selected do:

EditCopy
Then EditPaste SpecialValuesOK
Delete the cells with 0's
Delete the original table if desired

Biff

"Narendra Boga" wrote in message
...
hii all...

a small doubt about Transpose in Edit menu.

Here Iam spending much time to transpose each row into one column. There
is
any option to reduce my time on this issue?
The data is like this.......

A B C
1 12 13 14
2 15 16 17
3 18 19 20

I wanna arrage the values spread in A, B, C..into one A coulmn. I use
pastespecial--transpose. But it takes much time to arrange all rows. Is
there any other option to transpose all the data into one column???
I need the data like this...

A
1 12
2 13
3 14
4 15
5 16
6 17
7 18
8 19
9 20


Thanks,
Narendra

transpose multiple rows at once
 

You're welcome. Thanks for the feedback!

Biff

"Narendra Boga" wrote in message
...
Thank You very much. it is working. Thanq thnq

"T. Valko" wrote:

Try this:

A B C
1 12 13 14
2 15 16 17
3 18 19 20

Enter this formula in any cell:

=OFFSET(A$1,INT((ROWS($1:1)-1)/n),MOD(ROWS($1:1)-1,n))

n = the number of columns in the table. In your case n = 3.

Copy down until you get a contiguous return of 0 (meaning the data has
been
exhausted).

With the range of formulas still selected do:

EditCopy
Then EditPaste SpecialValuesOK
Delete the cells with 0's
Delete the original table if desired

Biff

"Narendra Boga" wrote in message
...
hii all...

a small doubt about Transpose in Edit menu.

Here Iam spending much time to transpose each row into one column.
There
is
any option to reduce my time on this issue?
The data is like this.......

A B C
1 12 13 14
2 15 16 17
3 18 19 20

I wanna arrage the values spread in A, B, C..into one A coulmn. I use
pastespecial--transpose. But it takes much time to arrange all rows.
Is
there any other option to transpose all the data into one column???
I need the data like this...

A
1 12
2 13
3 14
4 15
5 16
6 17
7 18
8 19
9 20


Thanks,
Narendra