+ Reply to Thread
Results 1 to 5 of 5

Transpose muliple rows to single column

  1. #1
    Registered User
    Join Date
    10-31-2011
    Location
    Yellowknife, NT
    MS-Off Ver
    Excel 2003
    Posts
    15

    Cool Transpose muliple rows to single column

    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
    Attached Files Attached Files
    Last edited by randinator; 11-01-2011 at 01:06 PM. Reason: Added second page to work-book.

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Transpose muliple rows to single column

    Hi Randinator, welcome to the forum.

    Thanks for sharing your data, however what do you want it to look like after the data is "transposed"? Please show that on Sheet2 and re-attach.

    Thanks!

  3. #3
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    157

    Re: Transpose muliple rows to single column

    Try...
    =INDEX(Raw!F:K,INT((ROW(E2)-ROW(E$2))/6)+2,MOD(ROW(E2)-ROW(E$2),6)+1)
    in Transformed!E2 filled down to Transformed!E2929

    Beau Nydal

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Transpose muliple rows to single column

    Try this. Enter this in E2 cell of Sheet Transformed and drag down.

    =OFFSET(Raw!$F$2,INT((ROW()-2)/6),MOD(ROW()-2,6))

  5. #5
    Registered User
    Join Date
    10-31-2011
    Location
    Yellowknife, NT
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Transpose muliple rows to single column

    Thanks. It worked.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1