copy and paste + transpone

[Menestrello]

Hi,
how can I easily copy values from this situation
column A
1
2
3
4
5
6
7
8
9
....

to this situation:
columnA - column B - column C
1 2 3
4 5 6
7 8 9
....

Thanks for your help!

 

[Claus Busch]

Hi,

Am Wed, 24 Jul 2013 17:48:01 +0200 schrieb Menestrello:

column A
1
2
3
4
5
6
7
8
9
...

to this situation:
columnA - column B - column C
1 2 3
4 5 6
7 8 9

try in B1:
=INDEX($A$1:$A$1000,(ROWS($1:1)-1)*3+COLUMN(A$1))
and copy to D1. Copy B11 down till you get zeros


Regards
Claus B.

 

[Claus Busch]

Hi,

Am Wed, 24 Jul 2013 18:30:50 +0200 schrieb Claus Busch:

=INDEX($A$1:$A$1000,(ROWS($1:1)-1)*3+COLUMN(A$1))
and copy to D1. Copy B11 down till you get zeros

Then copy B1n and paste special => Paste values => delete your column
A


Regards
Claus B.

 

[menestrinaguido]

Il giorno mercoledì 24 luglio 2013 18:30:50 UTC+2, Claus Busch ha scritto:

Hi,



Am Wed, 24 Jul 2013 17:48:01 +0200 schrieb Menestrello:







try in B1:

=INDEX($A$1:$A$1000,(ROWS($1:1)-1)*3+COLUMN(A$1))

and copy to D1. Copy B11 down till you get zeros





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Thanks, I tried that, but I got an error on the part "$A$1:$A$1000"

So I tried this other suggestion from another user:
in B1: =INDIRECT("R"&REF.ROW(A1)*3-2&"C1";0)
in C1: =INDIRECT("R"&REF.ROW(A1)*3-1&"C1";0)
in D1: =INDIRECT("R"&REF.ROW(A1)*3&"C1";0)
(... WELL i TRANSLATED HERE THE FORMULA FROM iTALIAN eXCEL... HOPE IT'S THESAME

And it worked fine!

Problem is: I lose formatting, and particularly the fact that every first of three rows contains an internet link.
Any suggestion?

Thanks for your help!