Transpose several columns of data

**judasdac** · 08-26-2009, 04:58 PM

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.

**contaminated** · 08-26-2009, 05:14 PM

I think u can use TRANSPOSE() function

**judasdac** · 08-26-2009, 05:46 PM

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

**Huron** · 08-26-2009, 07:42 PM

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.

**DonkeyOte** · 08-27-2009, 04:08 AM

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:

A2: =$B2&":"&$C2&":"&$D2&":"&$H2&":"&$I2&":"&$J2
ie: Project:Resource:Department:Work Unit:Pct Time:Req/UAT/Notes

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

**judasdac** · 08-27-2009, 03:00 PM

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