How to covert rows to columns or columns to rows.

[Imperial]

I have text in rows that are in separate cells that I’m trying to convert
into columns; which I figure out how to do by copying the rows click on the
cell and in the paste special box I then checking off transpose which lays
the copied rows into columns. Now what I’m trying to do is the same thing;
however I want to be able to have lets say 10 cells that I copy in a row this
time when it puts into columns I only want 5 cells in that column and the
next 5 cells of the 10 copied I want it to go in the next column below. What
it’s doing with the 10 cells that I copied in the rows now when I transpose
them into a column is putting all 10 copies cells into 1 column which I’m
trying to split the 10 cell rows into 2 columns and so on. It’s got to be a
format or formula thing just unsure how to go about this. If someone could
help me with this it would be greatly appreciated. Hope I was good in
explaining.

 

[Kassie]

Iso copying 10 cells in a row, just copy 5.

--
HTH

Kassie

Replace xxx with hotmail

 

[Imperial]

Kassie appreciate the response however I assume that; but I have over 2300
cells in a row copying 5 cells at a time would take all day.

 

[Imperial]

Kassie appreciate the response however I assume that; but I have over 2300
cells in a row copying 5 cells at a time would take all day.

 

[Kassie]

I actually expected as much! however, with the info at hand, it is
impossible to quote a macro to do the job for you! I need to know where you
are copying from, I need to know where you are transposing to! I need to
know what you are trying to achieve. What do you mean by the next column
below? Do you want to copy data in rows, and transpose them into columns, or
do you actually want to copy data in a column, and put it into rows?
It is also not clear whether you want to start pasting in say A1:E1, then
the next 5 in B2:F2, or in A2:E2. Or is it that you want to paste in A1:A5,
then B2:B6, or what?
The only way to hasten this process is through a macro, but then you have to
be able to tell the computer exactly what to do!

--
HTH

Kassie

Replace xxx with hotmail

 

[Sri]

So you want to transform

  A   B   C   D   E   F   G   H   I   J
  1   2   3   4   5   6   7   8   9  10
11  12  13  14  15  16  17  18  19  20

into

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

or what?

Hi,
Please find the below article. This should solve your problem. Let me
know if you have any questions.
http://funwithexcel.blogspot.com/2009/05/macro-to-transpose-rows-to-columns-or.html

 

[Imperial]

O.K! I’m copy from rows and pasting into columns. I’m copying data right now
from cell A:56 down to A:1819 which are rows and then I want to be able to
transpose that or paste that information into columns A:43 to I:43 which are
9 cells across in columns and the next command would be A:44 to I:44 and so
on. As it stands now I’m copying rows A:56 A:65 and transposing that into
A:43 to I:43 one at a time; whereas I’m wanting to carry out an automatic or
continuous command. Hope that’s helps and make sense. Thanks Kassie! ïŠ

 

[Imperial]

I want to do just the opposite of that which you post. It would look like
this; also see my latest post for more details

Copy
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Ect.

Then paste or transpose into

A B C D E F G H I
1 2 3 4 5 6 7 8 9
10 11 12 13 14 15 16 17 18 Ect.


A B C D E F G H I J
1 2 3 4 5 6 7 8 9 10
11 12 13 14 15 16 17 18 19 20

 

[Imperial]

I want to do just the opposite of that which you post. It would look like
this also see my latest post for more details

Copy
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Ect.

Then paste or transpose into

A B C D E F G H I
1 2 3 4 5 6 7 8 9
10 11 12 13 14 15 16 17 18 Ect.


A B C D E F G H I J
1 2 3 4 5 6 7 8 9 10
11 12 13 14 15 16 17 18 19 20

 

[Ragdyer]

Do you realize that after 117 cells (A43 to I55), you'll run out of room and
start overlapping your original data at A56?

This formula will copy your original data as you requested, over 9 columns,
then down as needed, BUT ... you can't overlap the original data.

You can enter this *anywhere*, and copy across and down, *as long as you
don't overlap the original data*!

Say you enter it in B43,
copy across to J43,
then down as needed..

=INDEX($A$56:$A$1819,(9*ROWS($1:1))+COLUMNS($A:A)-9)

 

[Imperial]

want to do just the opposite of that which you post. It would look like
this.

Copy
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Ect.

Then paste or transpose into

A B C D E F G H I
1 2 3 4 5 6 7 8 9
10 11 12 13 14 15 16 17 18 Ect.

I’m copy from rows and pasting into columns. I’m copying data right now
from cell A:56 down to A:1819 which are rows and then I want to be able to
transpose that or paste that information into columns A:43 to I:43 which are
9 cells across in columns and the next command would be A:44 to I:44 and so
on. As it stands now I’m copying rows A:56 A:65 and transposing that into
A:43 to I:43 one at a time; whereas I’m wanting to carry out an automatic or
continuous command. Hope that’s helps and make sense.

 

[Ragdyer]

My suggested formula will do *exactly* what you asked for!
Try it!

With your data already existing in A56 to A1819.

Enter this formula in A43:
=INDEX($A$56:$A$1819,(9*ROWS($1:1))+COLUMNS($A:A)-9)

Then, drag it across to copy to I43.

See what you get?
Now, select A43 to I43,
And drag down that 9 cell selection to copy to A55:I55.

See what you get.

NOW ... this is what I mentioned in my OP,
The next row, Row56, contains the start of your *original* data!

You can't overlap your original data when using a formula like mine.

Do you understand what I'm talking about?