How do I transpose multiple rows into columns?

I have data organized (in Office 2007) as such:

A
B
C

D
E
F

and so on

I want to tranpose it so it appears in columns as:

A B C
D E F

and so on.

Is there a way to transpose multiple rows so the information is stacked into 
columns?

Thanks a million!
0
Nick4562 (282)
12/21/2007 7:09:00 PM
excel 39879 articles. 2 followers. Follow

7 Replies
429 Views

Similar Articles

[PageSpeed] 39

Copy > PasteSpecial > Transpose.........as many times as you need......can be 
set to a macro if done frequently..

Vaya con Dios,
Chuck, CABGx3



"Nick" wrote:

> I have data organized (in Office 2007) as such:
> 
> A
> B
> C
> 
> D
> E
> F
> 
> and so on
> 
> I want to tranpose it so it appears in columns as:
> 
> A B C
> D E F
> 
> and so on.
> 
> Is there a way to transpose multiple rows so the information is stacked into 
> columns?
> 
> Thanks a million!
0
CLR (807)
12/21/2007 7:16:02 PM
Thanks.  If the information in the rows is different for each set, is there a 
way to copy and paste one time, or will I have to do it multiple times?

"CLR" wrote:

> Copy > PasteSpecial > Transpose.........as many times as you need......can be 
> set to a macro if done frequently..
> 
> Vaya con Dios,
> Chuck, CABGx3
> 
> 
> 
> "Nick" wrote:
> 
> > I have data organized (in Office 2007) as such:
> > 
> > A
> > B
> > C
> > 
> > D
> > E
> > F
> > 
> > and so on
> > 
> > I want to tranpose it so it appears in columns as:
> > 
> > A B C
> > D E F
> > 
> > and so on.
> > 
> > Is there a way to transpose multiple rows so the information is stacked into 
> > columns?
> > 
> > Thanks a million!
0
Nick4562 (282)
12/21/2007 7:26:02 PM
Multiple times, unless you are talking about MANY of them, or doing the whole 
thing frequently, then a macro would be best........if you're talking about 
something like names and addresses, someone has probably already done a macro 
to do the job.  If you can't find one, post back with more specifics and 
someone will help.

Vaya con Dios,
Chuck, CABGx3





"Nick" wrote:

> Thanks.  If the information in the rows is different for each set, is there a 
> way to copy and paste one time, or will I have to do it multiple times?
> 
> "CLR" wrote:
> 
> > Copy > PasteSpecial > Transpose.........as many times as you need......can be 
> > set to a macro if done frequently..
> > 
> > Vaya con Dios,
> > Chuck, CABGx3
> > 
> > 
> > 
> > "Nick" wrote:
> > 
> > > I have data organized (in Office 2007) as such:
> > > 
> > > A
> > > B
> > > C
> > > 
> > > D
> > > E
> > > F
> > > 
> > > and so on
> > > 
> > > I want to tranpose it so it appears in columns as:
> > > 
> > > A B C
> > > D E F
> > > 
> > > and so on.
> > > 
> > > Is there a way to transpose multiple rows so the information is stacked into 
> > > columns?
> > > 
> > > Thanks a million!
0
CLR (807)
12/21/2007 7:39:05 PM
Is there a blank row between C and D in your original datalist?
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Nick" <Nick@discussions.microsoft.com> wrote in message 
news:97395CAA-7359-441D-982B-3B12CF2109A7@microsoft.com...
> Thanks.  If the information in the rows is different for each set, is 
> there a
> way to copy and paste one time, or will I have to do it multiple times?
>
> "CLR" wrote:
>
>> Copy > PasteSpecial > Transpose.........as many times as you 
>> need......can be
>> set to a macro if done frequently..
>>
>> Vaya con Dios,
>> Chuck, CABGx3
>>
>>
>>
>> "Nick" wrote:
>>
>> > I have data organized (in Office 2007) as such:
>> >
>> > A
>> > B
>> > C
>> >
>> > D
>> > E
>> > F
>> >
>> > and so on
>> >
>> > I want to tranpose it so it appears in columns as:
>> >
>> > A B C
>> > D E F
>> >
>> > and so on.
>> >
>> > Is there a way to transpose multiple rows so the information is stacked 
>> > into
>> > columns?
>> >
>> > Thanks a million! 


0
ragdyer1 (4060)
12/21/2007 7:42:12 PM
No

"RagDyer" wrote:

> Is there a blank row between C and D in your original datalist?
> -- 
> Regards,
> 
> RD
> 
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Nick" <Nick@discussions.microsoft.com> wrote in message 
> news:97395CAA-7359-441D-982B-3B12CF2109A7@microsoft.com...
> > Thanks.  If the information in the rows is different for each set, is 
> > there a
> > way to copy and paste one time, or will I have to do it multiple times?
> >
> > "CLR" wrote:
> >
> >> Copy > PasteSpecial > Transpose.........as many times as you 
> >> need......can be
> >> set to a macro if done frequently..
> >>
> >> Vaya con Dios,
> >> Chuck, CABGx3
> >>
> >>
> >>
> >> "Nick" wrote:
> >>
> >> > I have data organized (in Office 2007) as such:
> >> >
> >> > A
> >> > B
> >> > C
> >> >
> >> > D
> >> > E
> >> > F
> >> >
> >> > and so on
> >> >
> >> > I want to tranpose it so it appears in columns as:
> >> >
> >> > A B C
> >> > D E F
> >> >
> >> > and so on.
> >> >
> >> > Is there a way to transpose multiple rows so the information is stacked 
> >> > into
> >> > columns?
> >> >
> >> > Thanks a million! 
> 
> 
> 
0
Nick4562 (282)
12/21/2007 10:00:05 PM
Say your datalist is in Column A,
Enter this formula *anywhere*, copy across 3 columns, then copy down as 
needed:

=INDEX($A:$A,3*ROWS($1:1)-2+COLUMNS($A:A)-1)

If you data would be in, perhaps, J20 to J100, then the formula would be 
adjusted to:

=INDEX($J$20:$J$100,3*ROWS($1:1)-2+COLUMNS($A:A)-1)

-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Nick" <Nick@discussions.microsoft.com> wrote in message 
news:E2B99198-274F-4227-A9B2-4D99FCC6BB64@microsoft.com...
> No
>
> "RagDyer" wrote:
>
>> Is there a blank row between C and D in your original datalist?
>> -- 
>> Regards,
>>
>> RD
>>
>> ---------------------------------------------------------------------------
>> Please keep all correspondence within the NewsGroup, so all may benefit !
>> ---------------------------------------------------------------------------
>> "Nick" <Nick@discussions.microsoft.com> wrote in message
>> news:97395CAA-7359-441D-982B-3B12CF2109A7@microsoft.com...
>> > Thanks.  If the information in the rows is different for each set, is
>> > there a
>> > way to copy and paste one time, or will I have to do it multiple times?
>> >
>> > "CLR" wrote:
>> >
>> >> Copy > PasteSpecial > Transpose.........as many times as you
>> >> need......can be
>> >> set to a macro if done frequently..
>> >>
>> >> Vaya con Dios,
>> >> Chuck, CABGx3
>> >>
>> >>
>> >>
>> >> "Nick" wrote:
>> >>
>> >> > I have data organized (in Office 2007) as such:
>> >> >
>> >> > A
>> >> > B
>> >> > C
>> >> >
>> >> > D
>> >> > E
>> >> > F
>> >> >
>> >> > and so on
>> >> >
>> >> > I want to tranpose it so it appears in columns as:
>> >> >
>> >> > A B C
>> >> > D E F
>> >> >
>> >> > and so on.
>> >> >
>> >> > Is there a way to transpose multiple rows so the information is 
>> >> > stacked
>> >> > into
>> >> > columns?
>> >> >
>> >> > Thanks a million!
>>
>>
>> 


0
ragdyer1 (4060)
12/21/2007 10:17:43 PM
Dear Nick,
i have used an add-in "tables transformer" to make columns out of rows, the 
link to it: http://www.office-excel.com/excel-addins/tables-transformer.html
Try it out, maybe it helps you too.
Best regards,
Evgeny

"Nick" <Nick@discussions.microsoft.com> wrote in message 
news:D4F93057-7B9B-4ED3-8D63-DDB050B9DB8D@microsoft.com...
>I have data organized (in Office 2007) as such:
>
> A
> B
> C
>
> D
> E
> F
>
> and so on
>
> I want to tranpose it so it appears in columns as:
>
> A B C
> D E F
>
> and so on.
>
> Is there a way to transpose multiple rows so the information is stacked 
> into
> columns?
>
> Thanks a million! 


0
12/25/2007 6:48:01 PM
Reply:

Similar Artilces:

VB- If first cell with formula is blank, all cells in column returns blank.
Hi all, I am using ADO to connect to an excel sheet and display the data in vb form. In excel there is a column named "TAT" which has a formula t add two othe cell values to it. If any of the two cells is blank the the TAT col remains blank. If the very first cell in the TAT column has some value then VB showa all cell values in that column. But if the first cell is empty then al cells in tat column are shown blank, even if there are values in othe cells. What's the solution for this? Thank -- Message posted from http://www.ExcelForum.com Hi Of course you can try with some...

Working with multiple worksheets
This is probably very simple but i am struggling with this. I have a workbook where one sheet will be updated on a monthly basis and i want the sheet preceding it to capture its outputs. A few things i need it to capture is to check if Col B has a specific value and if yes check the value of the cell adjacent to it and count this if it is a Yes or a No. Please help! BK It sounds like VLOOKUP will work for you. Look in the help index for VLOOKUP. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Balaji K. Arige" <v_2balar@hotmail.com> w...

After installing multiple updates, error code 0xC80001F
I have been installing multiple updates (due to computer restore). During this last set of updates, I noticed that there were a few that said they weren't installed (failed) successfully. I went back to the installed updates info, but there were no items that showed "failed". When I tried to go back to the Windows Update Home page, I received this error code [Error number: 0xC80001FE]. I do not have a 3rd party anti-virus or firewall active right now. I have done nothing following the installation of updates. -- Real integrity is doing the right thing, knowing tha...

Generic row reference in formula
I am using a formula in Excel to sum the values in a row: =SUM(B2:F2) Is there a way to use a generic row reference in a formula? For example, I don't know for sure at run time if the data and formula is going to end up in the second row. What I want is something like this =SUM(B#:F#) where the # sign would indicate the current row containing the formula. Thank you very much. Rick Quatro rickquatro@gmail.com One way: =SUM(INDIRECT("B"&ROW()):INDIRECT("F"&ROW())) -- HTH, RD --------------------------------------------------------------------------- P...

lost the from column for incoming emails
my incoming emails lost the column from, I don't know who sending me emails right click on the title bar where you would like to see the title FROM. At the bottom of the drop down list should be "customize current view". Click on it and go to Fields. Add it. "manny perez" wrote: > my incoming emails lost the column from, I don't know who sending me emails > ...

Text to columns #7
I have a worksheet that has the data in rows but I need them in columns. Is there a way that I can get the data to columns? ( example Rows 1,2,3 need to be Row 1 columns A, B C - Rows 4,5,6 need to be Row 2 columns A,B, C etc) hi, If i understand you correctly, i think you want to use the paste special - transposed. On the toolbar go Edit>PasteSpecial. in the dialog box(near the bottom on mine), check Transposed. This will paste data in rows into columns. Good luck FSt1 "jcross" wrote: > I have a worksheet that has the data in rows but I need them in columns. Is...

Multiple Pie Chart
How do you create a multiple pie chart? e.g. if you have 3 groups A, B and C, each which 3 different category X, Y and Z. Is it possible to put it all into one chart? I have done it but it has to be done individually and it takes time. There's no chart type that combines multiple independent pies in the same chart. Your approach (three sepaarate charts) is how I would do it. Unless you want to try one of the approaches in this article: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=471 These look great, combining the data into a single apparent chart, but they act...

concatenate cells from a column and paste to a cell
I'm trying to write a macro that will loop through a column and copy the data to a single concatenated string which will be pasted to another cell. The macro should step through the column of data until it reaches the end of the data set or an empty cell. I could name a range if needed. Any help getting me started???? The concatenate( ) function won't work with ranges..., so I assume I'll have to write a macro to loop. This will do it, but you may not be happy with the results since the resulting text string may be longer than Excel allows. This code runs from the cel...

Create two rows based on one row
A worksheet has information in a single row. The new worksheet has columns. How can I create two rows per column in this new worksheet, based on information from the single row (key=document number, same amount for debit- and credit - but in a different row, accounts based on table) . Hi not really sure about your spreadsheet layout. Could you post an example (plain text - no attachment please) and describe your expected result based on this example -- Regards Frank Kabel Frankfurt, Germany Toni wrote: > A worksheet has information in a single row. The new worksheet has > columns. ...

Two-column lookup help!
Greetings, I am having difficulty looking up an item using two-column lookup formulas as suggested. Following are the parameters of my situation 2 Different Workbooks Workbook 1 (Reference Workbook) Column b c d 125 Lay Foundation March 9, 2004 125 Purchase Frame mat. March 27, 2004 125 House complete May 15, 2004 267 Lay Foundation June 5, 2004 267 Purchase Frame mat. June 28, 2004 267 House complete August 29, 2004 Workbook...

Removing BLANK rows in Excel
WinXP Pro with Excel XP: How can I remove blank rows where there's nothing in any of the 12 columns that might contain data? The data has one row of valid data and then a blank row, alternating for about 5000+ rows. I'm receiving a file that, apparently, has a CRLF and giving me an extra row for each row with valid (useable) data. The supplier of the data can't strip off the CRLF. Without manually removing each blank row (row #s: 2,4,6,8 etc) each import from the *.csv file how can this be done automatically from within Excel? [The file is used weekly to migrate/update data fr...

Hiding column/row labels --without hiding entire columns/rows
Hello, I have seen worksheets that somehow hide all the default worksheet column and row labels (A, B, C, ...; 1, 2, 3, ...). The result can look stunning. But, I don't know how it is done. I have scaned through the worksheet properties, comparing it to normal worksheets which do display the column/row headers, but I can find nothing. Any hints on how to show/hide the default labels for a worksheet? Thanks. <Tools> <Options> <View> tab, And *Uncheck* all the things you wish to not display (hide). -- HTH, RD ============================================== Please keep ...

In Excel is it possible to hide a row condtional upon a cell val..
In Excel is it possible to hide a row condtional upon a cell value? with a macro yes,or you could format white text and all values would disapear. -- paul remove nospam for email addy! "Louise" wrote: > In Excel is it possible to hide a row condtional upon a cell value? The white text makes the text disappear, but not the entire row or column of cells. With a macro, is it possible to make the cells automatically unhide then if the data becomes relevant again due to changes? "paul" wrote: > with a macro yes,or you could format white text and all values woul...

Lookup last in column formulas
Hello Excel users and experts, Is there any significant difference in these formulas that return the last value in the column. =LOOKUP(9.99999999999999+307,A:A) =LOOKUP(9.99999999999999E+307,A:A) =LOOKUP(9.999+307,A:A) =LOOKUP(9.999E+307,A:A) Why use fourteen 9's past the decimal point, either with or without the E when three 9's with or without the E works just as well? Thanks Howard Hi! Good question. Here's a bone of contention I have when I see these types of formulas. Say for example that you know for certain that the absolute largest number that could possibly ...

Distribution Lists, Multiple copies sent?
I recently implemented an exchange server as part of a small business server install for our organization. We had previously hosted mail at our ISP. We had several listserv's there, which i converted to distribution lists by adding the people on the lists to our AD as contacts. When a staff member sends a message to this list, it appears to send multiple times (looks like four or five). What could be the causes of this? Thanks in advance for your help. ...

HEX2DEC whole row
I have never been to this newsgroup and I am astounded by the plethra of post on here. I look forward to using this resource. I have a task I have not been able to complete. I have an excel spread sheet that contains several colums. I would say each colum has at least 1000 lines. There is one particular column that has numbers in HEXADECIMAL format. Due to the enormous amount of data contained in this column it is not feasible for me to take each number and manually conver them to DECIMAL. I want to know if there is a formula I can plug in for the whole column that when I envoke it ...

locking data/cells so re-sorting a column doesn't re-position my d
I need to re-sort a column, but I want all the row's data associated with that row to stay with the SORT results. -- mtn_leisure mtn_leisure Wrote: > I need to re-sort a column, but I want all the row's data associated > with > that row to stay with the SORT results. > -- > mtn_leisure Try selecting the data in the other columns aswell and then sort the data should then stay together Paul -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&...

column alignment
How do I decimal align a column of numbers, them indent the numbers so they line up with the column heading text, which is center justified? Hi AFAIK this kind of format is not supported by Excel -- Regards Frank Kabel Frankfurt, Germany "ChSp" <ChSp@discussions.microsoft.com> schrieb im Newsbeitrag news:EB57AC71-2485-4835-B240-3150EA9A0DD3@microsoft.com... > How do I decimal align a column of numbers, them indent the numbers so they > line up with the column heading text, which is center justified? You could try to give them a format like: #,###.????? You'll...

Row height #9
Row height autofit isn't working for non-merged cells. Have ensure no spaces following text. What happens when you autofit that row? How much text do you have in that cell? Sharon wrote: > > Row height autofit isn't working for non-merged cells. Have ensure no spaces > following text. -- Dave Peterson ...

Concatenate info from columns into one row.
Hie, can anyone help with the following: COL A COL B COL C COL D Unit 1 Unit 1 3B Unit 1: 3B, 5LH, 6RH Unit 2 Unit 1 5LH Unit 2: 4B, 6LH, 7RH Unit 3 Unit 1 6RH Unit 3: 5B, 4LH, 9RH Unit 4 Unit 2 4B Unit 4 Unit 2 6LH Unit 2 7RH Unit 3 5B Unit 3 4LH Unit 3 9RH Unit 4 What i've got are 4 unique units, each of which have a handing assigned to them. I need to concat...

Adding Multiple Variables
I am using the following script to delete shares and want to specify multiple shares, instead of just one share name. How can I do so? DomainName = "domain.com" ServerName = "Server1" ShareName = "Share1" Set cont = GetObject("WinNT://"& DomainName &"/"& ServerName &"/LanmanServer,FileService") cont.Delete "FileShare", ""& ShareName &"" Also, how can I add to this script so that it deletes the directories associated witht he shares, once the shares are re...

Button macro to move/delete active row
I’m trying to learn from previous mistakes, which mainly seem to be around not planning well enough ahead and designing code on the fly! So I’m trying to be pro-active  Anyway, I’m planning a workbook with three sheets: New, Active and Archive. The sheets are basically the same with a row of headers at the top with work details entered in rows beneath. What I would like to do is have two buttons in the last row of the header labelled ‘Active’ and ‘Archive’ – having the buttons in the header so that I don’t have to have a button in each row. Then have a macro that can ident...

Update to Access Multiple tables via VBA
I'm somewhat familiar with updating from Excel to Access via VBA. How can I update to multiple tables in Access that have a one to many relationship using VBA. Table are linked via key. tbl_One is one Many with tbl_Two via Foreign Key tbl_One is one Many with tbl_Three via Foreign Key Set db = OpenDatabase("C:\LinkedTest\LinkTestDB.mdb") ' open the database Set rs = db.OpenRecordset("tbl_One", dbOpenTable) With rs ..AddNew ' create a new record ' add values to each field in the record .Fields(&...

Alowing for the Absence of Data from Multiple cells?
In my spreadsheet, I have those annoying #/DIV0! errors show up until enter data in the supplying precedents. I found a great tip but i only described a solution when there was one cell providing input t the calculating cell. I have cells that depend on 7 or more predents. Any help is appreciated -- Message posted from http://www.ExcelForum.com Juan, You could try =IF(ISERROR(my_formula),"",my_formula) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Juan >" <<Juan.1...

Opening calendar (month view), I want today's date on top row
I open and use calendar in the month view. if todays date is in the last week of the month, then it is in the bottom row. That means I see all of my appointments, etc. from the last month rather than those in my future. I would like the (uncompressed weekend) month view to open with today's date in the top row... is that possible? No, its not possible. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips....