convert data horizontal to vertical

**vorabha** · 09-05-2009, 09:30 AM

i have a data in columns and would like to convert into rows

e.g.
11 A B C D E F G H
22 1 2 3 4 5 6 7 8
33 2 3 4 5 6 7 8 9
44 3 4 5 6 7 8 9 0

and result should be
A 22 1
A 33 2
A 44 3
B 22 2
B 33 3
B 44 4
C 22 3
C 33 4
C 44 5
and so on ..

plz help

**di22y** · 09-05-2009, 10:34 AM

You canselect your data,use copy. Then select the top cell where you wouldlike the first item in the date to be placed, then use paste special(with the right mouse button) check the transpose box and ok it. This should achieve your goal. I think.

**vorabha** · 09-05-2009, 10:39 AM

thanks d22y
but this i already tried

result is
11 22 33 44
A 1 2 3
B 2 3 4
C 3 4 5
D 4 5 6
E 5 6 7
F 6 7 8
G 7 8 9
H 8 9 0

where as need sothing like
A 22 1
A 33 2
A 44 3
B 22 2
B 33 3
B 44 4
C 22 3
C 22 4
C 44 5
D 22 4
D 33 5
D 44 6

**zbor** · 09-05-2009, 10:53 AM

Try this:

If you have another input table you'll need to change formula slightly to match those inputs:

Book1.xls

**Special-K** · 09-05-2009, 11:31 AM

VBA solution, adjust loops as necessary

Please Login or Register  to view this content.

**DonkeyOte** · 09-05-2009, 11:46 AM

Special-K, just a quick note to say that in the below:

Dim i, j, k As Integer

i & j are both declared as Variants and only k is declared as an Integer, in VBA each must be declared explicitly, eg:

Please Login or Register  to view this content.

this is different from .NET

Also, in VBA Integers are converted to Long under the hood, there is no memory gain in using Integer in fact it's argued by some to be slower using Integer given the additional conversions that need to take place (Integer -> Long -> Integer) but it's case of preference of course.

**Special-K** · 09-05-2009, 12:11 PM

Thanks DonkeyOte, VBA is fairly new to me :-)

**Heitham** · 02-10-2013, 03:19 PM

Hi, I've a time series data as follow:
Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
1959 315.63 316.38 316.71 317.72 318.29 318.15 316.54 314.80 313.84 313.26 314.80 315.58
1960 316.43 316.97 317.58 319.02 320.03 319.59 318.18 315.91 314.16 313.83 315.00 316.19
1961 316.93 317.70 318.54 319.48 320.58 319.77 318.57 316.79 314.80 315.38 316.10 317.01
1962 317.94 318.55 319.68 320.63 321.01 320.55 319.58 317.40 316.25 315.42 316.69 317.70
1963 318.74 319.08 319.86 321.39 322.25 321.47 319.74 317.77 316.21 315.99 317.12 318.31
1964 319.57 320.00 320.76 321.84 322.25 321.89 320.44 318.70 316.70 316.79 317.79 318.71
1965 319.44 320.44 320.89 322.13 322.16 321.87 321.39 318.80 317.81 317.30 318.87 319.42
up to 2013

I don't know how to convert it into two raw as follow:

Jan.1959 315.63
Feb.1959 316.38
Mar.1959 316.71
Apr.1959 317.72
May.1959 318.29
Jun.1959 318.15
Jul.1959 316.54
Aug.1959 314.80
Sep.1959 313.84
Oct.1959 313.26
Nov.1959 314.80
Dec.1959 315.58

does any one have an idea?

**jeffreybrown** · 02-10-2013, 03:53 PM

Welcome to the Forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.