# copy and paste + transpone

M

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

Ad

C

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

C

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

Ad

M

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

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.