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

Advertisements

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 B1:D1 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 B1:D1 down till you get zeros

Then copy B1:Dn and paste special => Paste values => delete your column
A


Regards
Claus B.
 
Ad

Advertisements

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 B1:D1 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.

Ask a Question

Top