Convert Data Horizontal To Vertical


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
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
Similar Excel Video Tutorials
Horizontal Subtotals
 See Mr Excel and excelisfun try and create Horizontal Subtotals for a data set. See the IF, SUM and SUMIF functions as they try to solve this very dif ...
HLOOKUP ROWS Horizontal Lookup
 Learn how to use the HLOOKUP and ROWS function to retrieve multiple values from a Horizontal table.
This is a beginning to advanced Excel c ...
Similar Topics
i have data in horizontal formate but i want to convert it to vertical i.e in columns. how to?
Hi,
I have data in a horizontal format that I would like to convert into a vertical format.
The following is an extract of the data :
Postcode Trade 01Apr 02Apr 03Apr 04Apr 05Apr
AB A 1 1 1 1 2
AB B 1 1 1 0 1
AB C 1 1 1 1 2
AB D 3 3 2 2 4
AB E 0 0 0 0 1
AB F 1 1 0 0 1
AB G 0 0 0 0
AL A 3 3 2 2 4
AL B 1 1 1 1 2
AL C 1 1 1 1 2
AL D 3 3 2 2 4
AL E 6 6 5 4 8
AL F 1 1 1 0 1
AL G 1 1 1 1 2
AL H 1 1 0 0 1
The columns extend right out to 31Mar i.e one for each day of the year.

I have been asked to present the data, vertically, as follows :
Postcode Date Trade Amount
AB 01Apr10 A 1
AB 02Apr10 B 1
AB 03Apr10 C 1
AB 04Apr10 D 1
AB 05Apr10 E 2
i.e. each day of the year has to be listed against each category in the Trade column. This makes a total of over 300k rows. I have went through the painful process of copying and pasting (transposing) and I have done all the postcodes and dates.
Would anybody know any way of formulating the amount across from horizontal to vertical? I have looked into a few different formulas to no avail.
Thanks
Hi guys,
I was wondering if there is a way to convert a trimatrix (output of genetics software "MICROSAT") into a single column in excel. The resulting column needs to have both the vertical id (AG) and the horizontal id (AG) for each cell (06). Examples follow:
Trimatrix
id A B C D E F G
A 0
B 1 0
C 1 2 0
D 1 2 3 0
E 1 2 3 4 0
F 1 2 3 4 5 0
G 1 2 3 4 5 6 0
Column
vertical id, horizontal id, value.
A, A, 0.
B, A, 1.
C, A, 2.
D, A, 3.
E, A, 4.
F, A, 5.
G, A, 6.
B, B, 0.
C, B, 1.
D, B, 2.
E, B, 3.
F, B, 4.
G, B, 5.
C, C, 0.
D, C, 1.
E, C, 2.
F, C, 3.
G, C, 4.
ect.
The trimatrix I am trying to convert has about 20,000 records.
Any help would be much appreciated.
Cheers,
Dave
Is this possible ro do I need to convert to vertical? If so how do I convert to vertical? Would prefer horizontal autofiltering if possible though'
thanks
Kindly please help to convert from Horizontal To Vertical Format. Its about 1000 rows The sample data are,
Item Desc Item2 Desc2 Qty
1 A 1A 1AA 1
1 A 1B 1BB 2
1 A 1C 1CC 3
2 B 2A 2AB 1
2 B 2B 2BB 7
3 C 3C 3CC 10
Results Required will be,
Item Desc Item2 Desc2 Qty Item2 Desc2 Qty Item2 Desc2 Qty
1 A 1A 1AA 1 1B 1BB 2 1C 1CC 3
2 B 2A 2AB 1 2B 2BB 7
3 C 3C 3CC 10
Thank you.
If I have a list of emails that are separated by a comma, and I cut an paste them into a spreadsheet, and then do a text to columns function, all of the emails get separated nicely into separate cells. Is it possible to then take those horizontal cells and convert them to vertical cells, so that I could sort them? I.E. Take info in Cells A2 to Z2 and put it into cells A2 to A27.
Dear All,
i have more than hundred workbook and want to fetch data fro all the workbook.
i want to fetch data from horizontal to vertical .
Data format is c6:j6 total eight values c7:j7 upto c21 to j21 total 128 values.
this 128 values which was horizontal i want to convert it into vertical col.
i.e from D1 to D128 vertical.
Hope u understand my problem.
Rgds,
aligahk06
Hi,
I need to create a multipage with tabs running down the left hand side. I cannot use the normal horizontal multipage control because this would result in an impossibly wide page. Is there a vertical multipage control? I could not find one? Or a way to convert the multipage control to show vertical tabs?
Please bare in mind that this vertical multipage will be shown in a form that already includes a normal horizontal multipage (I am using multipages within multipages too!).
Many thanks for your time and advice.
Dom
I am trying to convert repeating excel data from vertical to horizontal.
Example:
(original data)
name
phone
email
name
phone
email
(ect)
to:
name phone email
name phone email
or in excel terms I am trying to do this:
from
a1:
a2:
a3:
a4:
a5:
a6:
(continuing)
convert to
A B C
a1: a2: a3:
a4: a5: a6:
(ect)
Please help, thanks
Hi All,
I have list of numbers that is near about 500, now the problem what I am facing is,the list is in vertical format and I need it in Horizontal way
e.g.
123
145
To
123 145
Please help.
Regards,
Rupendra Mehta
I am trying to convert a column of numbers into hours:minutes:seconds.
For example, I have following numbers (original data followed by desired output). I tried using the custom number format but could not get the desired result. Any assistance appreciated.
9  convert to :09
53  convert to :53
68  convert to 1:08
109  convert to 1:49
121  convert to 2:01
262  convert to 4:22
How can I convert a table of veerticle data to horizontal. Assume I have a list from a1:a10 and I want it go horizontabl from b1:k1
Thanks
Hi,
A study was conducted and data was collected on the same measures at different time periods (dates). It is recorded in the vertical column (sample attached). I want to get the data in the row.
What I don't know is, how to convert it in a way that each date takes a different position eg. first date should come under date 1, second under date 2 and so on and all the corresponding data comes against those dates.
Please find attached, sample of data and horizontal row is created to show the format which is required (yellow highlight).
Thank you for the help.
OK...This is supposed to be easy, and it's driving me crazy, as usual. for example, If I have a range of numbers from A2:E7. How do I convert them all to either one row, or one column? Another way to say it: How can I select the entire box of numbered cells, and then have excel convert all numbers to a row or column? The reason I ask is the Descriptive Statistics function (in Data Analysis) must be styled in either row or column...not both.
Thanks for the help
When I was using Excel 2000, there was an Excel addin where I could highlight rows of information and then transpose these into columns of information. Since we have upgraded to Excel 2003, the same Excel addin does not work and I have not been able to find a simple solution to transpose my information from a horizontal view to a vertical view or the reverse.
Or do I just have to move 53 columns (weeks) of 4 rows one cell at a time to 4 columns of 53 rows (weeks)?
Anyone have any ideas on how to make this happen?
i have formulas in rows like:
a1 b1 c1 d1 etc to IF1
=TDY!h1 =TDY!h2=TDY!h3 =TDY!h4 ..... =TDY!h239
and i want to convert them to vertical, but when i do, they do not paste in the save formula. i do not want the formula to change, only the 239 cells to go from horizontal to vertical
Hi,
I have a data in rows and I want to convert it to vertical columns while keeping (and duplicatin) the Id columns in order to import it to my DB later.
Please see an example:
this is how I get the data:
id first last City zipCode
1 Dan Mano Neyyork 12345
2 Deby Roger Chicago
3 Ben Stiller Boston 4555
This is how I eventually want it to be:
Id ItemValue
1 dan
1 Mano
1 NewYork
1 12345
2 Deby
2 Roger
2 Chicago
3 Ben
3 Stiller
3 Boston
3 4555
As you can see if there are blank cells I ignore them.
Is there a solution to my problem?
thanks in advance
Omer
Hi
I am new to this forum and new to Macro
I am attaching a file where horizontal row should be converted into Verticle Row
Can you pls help me with it
thanks a lot in advance
I need a solution for the equivalent of a SUMIF combining both vertical and horizontal data. The vertical cells align to the horizontal ones, but they're in a different table.
My attempted formula is: =SUMIF($H$22:$H$30,"TRUE",D7:L7)
*note that this is just an example set of data...my real data set is much larger (both rows and columns)
I need to be able to do this without transposing any of my data.
Things I've tried:
 Another option I tried was making D7:L7 a named range and using the transpose function (as an array) within the SUMIF formula above. I received an error.
 I tried using a bunch of IF statements added together (i.e. =IF(H22=TRUE,D7,0)+(H23=TRUE,E7,0)...); this actually works properly, but I get the "formula too long for cell" error when I put them all in (too many characters)
Can anyone help with a solution?
I'm using excel 2003 and windows XP professional.
Thank you!
Lindsay
I have data that goes into excel in vertical rows but I need it to go horizontal.
Is there a way to do this without manual changing?
Thanks
Jesse Jutkowitz
Sir.how to convert i entered in some numaric data.to convert to columns the same data.
Hi,
I have a source file which has data arranged like this
A1
A2
A3
A4
B1
B2
B3
B4
and I need to convert it to a sheet that looks like this
A1 A2 A3 A4
B1 B2 B3 B4
Is there an easy way to do this  I have about 300 addresses and the transpose choice (whilst it works) would be really slow (I think). Please help  feel like such a newbie
I have been stuck on this problem and hope someone here can help out. I have a data set that is arranged horizontally in a table and I need it in just a vertical list. All of the searches I have done point to the transpose option under paste, but that doesn't work for this problem. The attached file shows an example of how I would like to have the data arranged. Thanks for the help!
I have a chart (price sheet) with horizontal and vertical titles. Horizontal value is name of item and vertical value is supplier name, the chart is filled with numeric prices. I want to sort the horizontal plane and give as a result the supplier name in decreasing order.
A B C
Example: plates 1 3 2
the result: A;C;B
what function can I use?
Hi Guys,
I have few data which consists of Vertical and horizontal headings with values. These vertical and horizontal headings may vary its position but I need the correct value from the main sheet.
State Not So Good General Good
Mumbai 51 36 74
Dehi 33 27 46
Kerala 35 24 73
AP 34 27 46
Karnataka 56 24 78
Chennai 23 26 54
Kolkata 48 36 76
Gujarat 64 42 43
The above details will be the main sheet which may vary the horizontal and vertical headings.
IN the next sheet I will have some fixed headings which should pull the data from main.
Any help would be appreciated.