Email:      Pass:    Pass?
Subscribe for Free Excel tips & more!
E-mail:
Advertisements


Free Excel Forum

Convert Data Horizontal To Vertical

Forum Register
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

Helpful Excel Macros

Convert Numeric Dollar Values into Text in Excel - UDF
- Convert a numeric dollar amount into its text equivalent. This UDF (user defined function) for Excel will change or tra
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Delete Rows in Excel if Completely Empty
- This macro will delete only completely blank rows in an excel spreadsheet. It allows you to make a selection of rows, ru
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt

Similar Topics









I have data laid out horizontally that needs to be vertical re-using most of the fields, however when I copy and paste the formulas, excel jumps forward the number of cells in the sequence (16), I need to continue out this pattern for 600+ instances

='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!M2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!N2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!O2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!P2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!Q2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!R2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!S2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!T2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!U2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!V2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!W2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!X2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!Y2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!Z2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!AA2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!AB2


='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!M3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!N3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!O3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!P3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!Q3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!R3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!S3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!T3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!U3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!V3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!W3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!X3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!Y3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!Z3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!AA3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!AB3

the only variation is the last column of cells it references the horizontal data, the rest of the data is repeated 16 times. Anything short of a find and replace after a copy paste would make my life easier.



Please find attached file , Please help me to convert vertical data to horizontal data

the actual data is data sheet and my desire result is in expected result sheet.

Please provide me with formula / function to achieved the expected result

Thanks & Regards

Muruganand

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 01-Apr 02-Apr 03-Apr 04-Apr 05-Apr
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 31-Mar 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 01-Apr-10 A 1
AB 02-Apr-10 B 1
AB 03-Apr-10 C 1
AB 04-Apr-10 D 1
AB 05-Apr-10 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 tri-matrix (output of genetics software "MICROSAT") into a single column in excel. The resulting column needs to have both the vertical id (A-G) and the horizontal id (A-G) for each cell (0-6). Examples follow:

Tri-matrix
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 tri-matrix 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





I'm in need of a little assistance, if someone can help. I have some vertically formatted data that I need to reformat into horizontal data so it can be used in various reporting/graphing tasks.

I'm more of an "Access-guy", and my customer needs this to be in Excel, so please forgive me if this is a "newbie" question...

Background... The source table/sheet has 27 columns, and the "unique key" is comprised of columns B,F,J, & X (bill_to_customer_no, ship_to_code, sku, & period) Columns A-W will effectively be identical (summarizing into a single record in the end), with column X (period) being the column that needs converted into columns. Columns Y-AA (forecast, budget, actual) will be the values that need to be parsed into the period columns Forecast (final_forecast) Budget (reference_4) Actual (actual_history)
I've attached a sample spreadsheet with an example of how the source data will appear (Data tab), and an example of how I need the end result to be (End Result).

Any assistance will be greatly appreciated.

Thanks,
7


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.




I have a row that is 400 columns wide with values sporadically thrown around in it. Majority of the values are blanks. Is there a way, without using visual basic, to put these nicely into a single column without blanks, using formulas only? There will never be more than 50 non-blank values, thus no more than 50 cells with formulas. I attached a sample file so it's a bit easier to understand what I am after. Thank you!
Horizontal to Vertical Example.xlsx

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




Hi All,

I would be greatful if somebody would suggest me how to arrive the required values from one sheet to another also from vertical to horizontal.Herwith
attached the excel which would explain in better way
Please help me!!!!!!!!!!!!!!!!!!!!!!Required format.xlsRequired format.xlsRequired format.xls

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


When I was using Excel 2000, there was an Excel add-in 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 add-in 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 a problem that I'm not sure transpose can fix.

I have a database aligned horizontally. It covers 25 items, and then 40 sub-categories of each item. So, every 40 columns, everything starts over with data for the next item. So columns 1-40 cover item 1, columns 41-80 cover item 2, ... through all the items. I want to change it to a vertical format where the subcategories lineup. So if there are 10 rows that track the sub-categories over time, I'd like the data for ITEM 2 in rows 1-10 of columns 41-80 to be placed in rows 11-20 of columns 1-40 (under ITEM 1 in rows 1-10 of columns 1-40), and ITEM 3 data in rows 1-10 of columns 81-120 to go into rows 21-30 of columns 1-40, etc. through all 25 items.

1 item = 40 columns and 10 rows.
Goal is to get it to where instead of them being laid out side-by-side horizontally, they are rather one-on-top-of-the-other vertically.

Rows 1-10 are dates, and the 40 columns are data points I'm looking at for each item. The horizontal layout gives me data by date. I need to have the data organized vertically by the data points (the 40 columns) to run regression analysis in another program.

I'd like not to do it through grunt work and I think it would be helpful to learn how to do this. Any ideas?

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


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 Ney-york 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 New-York
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




Hello Friends,

I am in trouble of converting Vertical Columns to Horizontal Rows. I used Transpose function but I have too many data to perform the Transpose function.

Is there any other formula to do it in simple manner, which can work on large data smoothly??

My data file is attached here with for your reference.

Thanks in Advance.