Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Transpose Horizontal Rows to Vertical Columns

Hello Fellow Excel Users,

I am in need of some expert advice. I have a file that looks like this:

Acct #11019145036954564872011019125101521103192103103904205851103192415102

The file is broken down by revenue codes on the first row of the account and then units on the second row. (450= Revenue Code and 1=Units associated with that revenue code) Then a new account number starts and it is revenue codes on the first first row and units on the second row.

I want the file to look like this:

Acct #Rev CodesUnits11019145011101913903110191680211019172031101911104110319394311031939215110319545101103196481
Any thoughts on how to do this? I am working with very large data amounts of data, so I can't just use a simple copy and paste transpose funtion, I believe I need to use a script.

Thanks so much for any advice!!


Post your answer or comment

comments powered by Disqus
If I transpose data (horizontal row to vertical column)from one sheet to
another with the "copy" and "Paste Special" command using "transpose", the
programme only gives the values. I need to keep the link to the original
sheet to ensure that if any data is changed in the original sheet (row) that
it changes in the column on the new sheet. I have tried the "Transpose"
@-function unsucesfully!!

Hello,

I have a very large amount of data (workday time punch data) that I need to
alter from multiple rows to multiple columns on one row. See small example
of data as follows where FirstName, LastName, Badge, Store, ScanDttm, RAW,
CONVERTED are column headings (A1 thru G1):

FirstName LastName Badge Store ScanDttm RAW CONVERTED
ROBERT H. CASAVAN 600265 1211 1/2/2004 28:00.0 9:28:00 AM
ROBERT H. CASAVAN 600265 1211 1/2/2004 57:00.0 2:57:00 PM
ROBERT H. CASAVAN 600265 1211 1/2/2004 24:00.0 3:24:00 PM
ROBERT H. CASAVAN 600265 1211 1/2/2004 03:00.0 5:03:00 PM
BRIAN BAUM 600760 1211 1/2/2004 36:00.0 9:36:00 AM
BRIAN BAUM 600760 1211 1/2/2004 05:00.0 3:05:00 PM
MICHAEL ANTOSIK 600820 1439 1/2/2004 52:00.0 9:52:00 AM
MICHAEL ANTOSIK 600820 1439 1/2/2004 06:00.0 1:06:00 PM

I want to take the CONVERTED data (time punch in & out time) for ROBERT H.
CASAVAN on 1/2/04 (ScanDttm) and spread those 4 in & out time entries into 1
row and multiple columns. Then the same for BRIAN BAUM, and the same for
MICHAEL ANTOSIK, etc. Each individual will have from 1 to 4 in & out entries
on a given day.

Is there a way to accomplish this without copying and transposing each set
of in & out time entries for each person and each day separately? If so, how
do I do that?

Thank you!
--
heyredone

Hi,
I need to create a macro to move variable multiple horizontal data to vertical format with certain infomation on horizontal will be duplicated following that variables. It's looks like below where you can see variables data in column F, G, H and I are moved vertically and at the same time column A, B, C, D and E will be duplicated following the variables allocation. I've tried to use transpose but it too manual and now looking suitable macro to help on this function

Original DataAccountDim 3Dim 4AmountCurrencyV20228V20242V20211V202044006003300BXXX 9.4USD0.591.923.343.554006003400BXXX 88.17USD5.5118.0331.3233.314006003500BXXX 7.27USD0.451.492.582.75Process to automateAccountDim 2Dim 3Dim 4AmountCurrency400600V202283300BXXX 0.59USD400600V202283300BXXX 1.92USD400600V202283300BXXX 3.34USD400600V202423300BXXX 3.55USD400600V202423400BXXX 5.51USD400600V202423400BXXX 18.03USD400600V202113400BXXX 31.32USD400600V202113400BXXX 33.31USD400600V202113500BXXX 0.45USD400600V202043500BXXX 1.49USD400600V202043500BXXX 2.58USD400600V202043500BXXX 2.75USD

Is there anyone can help?

I am new to vba and would like some help. Is there an easy way to copy row 1 of sheet1 to column a of sheet 2.

How would you make a loop that would continue this logic for 20 rows.

Appreciate any advice,

April

example.xlsx

Hi
I am looking for some sort of automatic copy-paste option, that knows to transpose a row into a column, but keeps pointing/referring to the original data of the row.

In the file above I created an example of my question:
I have some data in row 1
I would like this data to be copied into column g, so that every time I change the data in row 1, the data will automatically change in column g as well.
If I use the "copy-paste with transpose" option, only the values are updated, so that's not good enough, because when the values in the row change, they don't change in the column.

thanks

Hi

Nice to meet you all here. I am a newbie in vb and also a new member to Excel Forum.
I have encountered some difficulties in creating macro in excel where I need to copy data from Sheet1 row A1:C3 into Sheet2 column A1:C3 as well. Which means Sheet1 is a table whereas Sheet2 is a form. The idea now is to copy the data horizontally(row) become vertically(column) in each wordsheet respectively. Attached is the sample showing the simple requirement. Appreciate helps from anyone. Thanks.

Hi there. Very novice user here. I'm trying to transpose multiple rows of data into a single column. I've attached an example of the data I'm working with.

Hi there. I provided an example of the output on the sheet marked 'Transposed". What I'm really interested in is transposing the values. I can add the other columns of data/dates etc. once I have the values transposed and in a single column. Note that the values are hourly, so there are always 24 values per day. The sheet labled 'Raw' contains the original data-set, and each day is broken into four rows of six values in each row. What I did to create the example is copy the first row of six values and transpose them into a single column, then copy the second row and transpose/paste these onto the bottom of the single column and so forth.

Randy

I need to move address information from multiple rows to multiple columns however, the address information is not uniform. Some customers have 4 lines of address information while others only have 2. I've attached a sample of the data. Any help would be greatly appreciated.

Would like information to look like

Name | Address | Suite # | City, State ZIP | Address 1 Fax | etc.

Thanks

I need to move address information from multiple rows to multiple columns however, the address information is not uniform. Customer names are in different columns while address information is in another and occupy several lines.

I've attached a sample of the data and a sample of what we need the information to look like. I've tried to apply some answers from other posts but my knowledge of excel is average and I've been unable to make anything work. Any help would be greatly appreciated.

My transcripts need to be converted from horizontal text into horizontal text
in vertical columns..make sense?

how do you transpose one row into 3 columns, for example:

515222424224111424243524525224525 to

515
222
424
etc...

Originally Posted by PCI Assuming data from A1 to F3
H1=MOD(COLUMN(H1)-COLUMN($H$1),6)+1
H2=INT((COLUMN(H2)-COLUMN($H$2))/6)+1
H3=INDIRECT(ADDRESS(H2,H1))
Copy and drag to the right until needed
You can merge formulas

I know I should know this but I can't remember.

How do I copy a horizontal row of data from one worksheet into a vertical row in a new worksheet?

I need to paste link fron horizontal cells to vertical cells. tks

one way

In B1 place the formula

=IF(MOD(ROW(),2)=1,A2,"")

Copy it all the way to the bottom - you should get alternating rows where
the switch # lines up next to the correct address#

If so, copy the entire column B, the Edit>Paste Special Values

If you need to keep all of these in the original order, insert a helper
column & fill it with a series of ascending numbers.

Then sort the entire bit on column B & delete all the rows where column B = ""

If needed, resort on the helper column to return the list to the original
order

"ad" wrote:

> I am working on a sheet that has more 11000+ rows. I need to move alternating
> rows to a column without having to do it manually. However, currently A1
> correspondes with A2, A3 with A4, etc. How can I move it from:
>
> address1
> switch#1
> address2
> switch#2
>
> to:
>
> address 1 switch#1
> address 2 switch#2
> address 3 switch#3

I searched every where but couldnt find anything that would help me...........
ALL I WANTED WAS TO CUT THE COLUMN(A) OF ODD NUMBERED ROW TO THE COLUMN(B) OF PREVIOUS ROW.........
Could anybody please help

I am working on a sheet that has more 11000+ rows. I need to move alternating
rows to a column without having to do it manually. However, currently A1
correspondes with A2, A3 with A4, etc. How can I move it from:

address1
switch#1
address2
switch#2

to:

address 1 switch#1
address 2 switch#2
address 3 switch#3

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

I know the transpose option under Paste Special menu will arrange data from
horizontal to vertical and vice versa. Is there a function or formula that
will re-arrange data from multiple columns/rows into one row? For example,
from 8 columns across and 3 rows down to 24 columns across and 1 row down?
(I have 1000 rows of data, so manually would be a time-consuming process).
Thank you.

I have a data set I need to transpose from rows to columns.

There are approx 10K rows on the sheet and each set of data is 102 rows all sets are the same size and the data repats itself in order

Attached is an example of the data and how it needs to be transposed.

Thansk in advance

Mark

I am trying to create a formula to have a cell in Column B on sheet1 (B12) equal to the data found in Row 39 on sheet2 (B39).
This is no problem, I used
=+'Sheet1'!B39
or even
='Sheet1'B39

The problem is that when I want B13 on Sheet1 to equal C39 on Sheet2, I have to type in the individual formula in order to make it work. If I copy and paste it Sheet1 B13 equals Sheet2 B40 (wrong column, wrong row).

I tried a formula like this:
=INDIRECT(ADDRESS(COLUMN()-26,2))

but it only worked for the same sheet and even then I couldn't figure it out.

Anyone know of a way for me to copy and paste the formula so that the orientation (data retrieval from Sheet2) will be horizontal rather than vertical?

,Thanks
Justis

Good day all,

I have two sources of data which are local tables in my database. I would like to take these and transpose specific rows to columns.

Thank you.

I have the following macro which I use to transpose a number of columns in one sheet to rows in another sheet.

PHP Code:
Sub task1()
Dim i As Integer, n As Long
For i = 5 To Columns.Count Step 2
    n = n + 1
    With Sheets("Working_Checklist_1").Cells(10, i).Resize(16)
        Sheets("Summary_Intermediate").Cells(n + 2, "c").Resize(.Columns.Count, .Rows.Count) _
        .FormulaArray = "=if(transpose(Working_Checklist_1!" & .Address & ")=0,"""",transpose(Working_Checklist_1!" & .Address & "))"
    End With
Next
End Sub 
I'd like to change this macro so to transpose from rows to columns. I've tried a couple of things, but can't quite get it to work.

I'd like to transpose every second cell starting from E7 to IV7 in Working_Checklist into column BU in Summary_Intermediate, starting from BU3.
Can anyone help with this?

I have thousands of rows of data in the following format (in Excel 2000):

Date Parameter Value
3/10/79 Temp 22
3/10/79 Oxygen 2.5
4/1/80 Temp 25
2/24/81 Temp 23
2/24/81 Oxygen 1.0
2/24/81 pH 7.0

I want to change it to:

Date Temp Oxygen pH
3/10/79 22 2.5
4/1/80 25
2/24/81 23 1.0 7.0

I could (and have) manually copy and transpose the values for each date.
However, as in my example, each date may have a different set of parameters.
Therefore, the resulting rows would not have the same number or even name for
resulting columns. This makes the transposition very time-consuming, as I
must transpose and then move the data into the correct columns.

I have seen formulas that will automate row-to-column conversion for set of
values that repeat (like every group of four rows in a column converts to a
row). The suggestions were like the formula below:
=OFFSET($A$1,(ROW(1:1)-1)*4,0)

Is there any way to modify this for my situation? Each group that needs to
be transposed has a unique date.

Thank you

I have a worksheet with 1268 rows and 31 columns. Ideally I need all the
rows to be converted to one column, although I believe this is impossible
without some additional manual steps.

Every time I attempt to copy/transpose my rows to columns, I receive the
message that the paste area does not match the size of the copy area. I have
tried transposing a few rows at a time. I have tried transposing ONE row at
a time. Each time I try, I either receive an error message, or get the
#VALUE! error message in the cell. I've tried TRANSPOSE and MINVERSE.

I would prefer NOT to do this manually as I have about 30 worksheets of a
similar size, and I'd really like to have my thesis done in five
months...sooo...if anyone has any ideas for me, they would be much
appreciated.

As well, if Excel cannot handle an action of this size, does anyone know any
other programs that might??

Thanks,
Suzanne


No luck finding an answer? You could always try Google.