+ Reply to Thread
Results 1 to 6 of 6

Transpose several columns of data

  1. #1
    Registered User
    Join Date
    11-14-2008
    Location
    Brooklyn, NY, USA
    Posts
    53

    Question Transpose several columns of data

    Hi All,
    I'm working on this project that I inherited from another colleague and am at a sticking point. The workbook is used to determine which employees are working on which projects what pct of the time. The workbook was set up using a start and end date (columns e and f) for the projects instead of a column with the month and the correct percentage. I've set up monthly columns, which are now in columns K through AF. I believe that I need to transpose those columns into a row so that I can set up a pivot table.

    Any ideas about the best way to go about doing this? I know I could do it manually, but I'd prefer not to do so.

    I've attached the worksheet if that helps at all.

    Cheers and thanks in advance for the help.
    Attached Files Attached Files
    Last edited by judasdac; 08-27-2009 at 03:01 PM.

  2. #2
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Transpose several columns of data

    I think u can use TRANSPOSE() function
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, ?Born in USSR?
    Vusal M Dadashev

    Baku, Azerbaijan

  3. #3
    Registered User
    Join Date
    11-14-2008
    Location
    Brooklyn, NY, USA
    Posts
    53

    Re: Transpose several columns of data

    Thanks for the response. I think my problem may be a little bit more complex. I could transpose 22 rows of data for each, but that doesn't seem so efficient because each resource may only work on a given project for one or two months rather than the entire period.

    I've started hacking away at this manually, but any advice to speed it up would be appreciated. Thanks

  4. #4
    Valued Forum Contributor
    Join Date
    08-23-2009
    Location
    Hampshire, UK
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    328

    Re: Transpose several columns of data

    I think I've managed to do what you want with the data using a file which I set up a few years ago to transpose excel tables to use in access databases. I've added a new sheet in your file called DATA and made a pivot table from that. (I've only attached a part of the Transpose file that I used as it's too big to upload)

    I'm not sure that your % columns make sense because your start dates are end of months but you put values for those months eg if a person starts 30th June and finishes 31st July, they have a value in June and July. This seems to give quite a few double-dips.
    Attached Files Attached Files

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Transpose several columns of data

    My advice would be to use a Multi Consolidation PT, see this link for an intro:

    http://datapigtechnologies.com/blog/...-a-pivottable/

    Basic premise, recreate your Resource List sheet on a new sheet, unhide all columns... insert a new column A and create a concatenation of important data, eg:

    Please Login or Register  to view this content.
    -- Copy paste results of A over A as values (ie remove formulae)

    -- Delete columns B:J leaving only the above concatenation and the Month columns.

    -- Create the Multi Consolidation PT using the A:J as your range with 0 Page Fields (click Finish)

    -- On resulting PT drill into Grand Total.

    -- Insert 6 columns between A & B on drill through sheet

    -- Run Text to Columns on Column A data - using Delimiter of :
    (apply colon delimiter via "Other")

    You will then have your data setup without need for formulae etc. (in a matter of minutes)

    EDIT: I've attached a zipped version of the output so you can see results of the above process
    Attached Files Attached Files
    Last edited by DonkeyOte; 08-27-2009 at 04:16 AM.

  6. #6
    Registered User
    Join Date
    11-14-2008
    Location
    Brooklyn, NY, USA
    Posts
    53

    Re: Transpose several columns of data

    Wow!!! Thanks so much for the responses. Should I get something like this dumped on my lap again, I have good techniques for sorting out.

    More proof that the participants in this community are the best on the web

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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