+ Reply to Thread
Results 1 to 4 of 4

Complex data table into pivot table

  1. #1
    Registered User
    Join Date
    05-22-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    12

    Complex data table into pivot table

    Hi there

    I am a newbie and have a problem with getting a detailed data table into a pivot table format. Basically I have 150 staff members in a column and they are allocated across 100 project codes going across a row. So each staff member is allocated against several projects. I have many empty cells where staff are not allocated as well. The data is in one table is hours which is then used to allocated a value in another table, this 2nd bit is fine as if I can allocate the hours properly I can write a formula to calculate the value. However I need to be able to get the staff member in one column and the hours on whichever project they are allocated to in another column. The data is also dynamic as a new member of staff can be added, the hours allocation can change but also I may need to add a new project at some point too. HELP! I tried using IF formulas to pick out the data but I cannot get any further can you help me? Below is a basic illustration of the data table:
    ----A---------B---------C-----------D------
    1)---------Project A--Project B--Project C
    2)Staff 1 5 30
    3)Staff 2 1 34
    4)Staff 3 35

    How do I get this to be:

    Staff 1--Project A--5
    Staff 1--Project B--30
    Staff 2--Project A--1
    Staff 2--Project C--34
    Staff 3--Project B--35

    I hope this makes it easier to understand what I am trying to do. I fear I may need a macro of some sort which I don't know how to write, if you can help with this that would be great.

    Thank you in advance for your help.

    Andrew

  2. #2
    Registered User
    Join Date
    05-22-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Complex data table into pivot table

    I was just researching and found out about the pivot table wizard that does multiple consolidation of data, will this work for my problem do you think? I am going to try it tomorrow.
    Thank
    Andrew

  3. #3
    Registered User
    Join Date
    11-28-2011
    Location
    Bangalore
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Complex data table into pivot table

    Hi Andrew, I'm stuck with a similar requirement. Could you find any solution?

    Thanks

  4. #4
    Registered User
    Join Date
    05-22-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Complex data table into pivot table

    Quote Originally Posted by vamshik27 View Post
    Hi Andrew, I'm stuck with a similar requirement. Could you find any solution?

    Thanks
    Hi If I remember correctly I did use a combination of data consolidation and recorded a macro so I could easily make amendments without it being too time-consuming each time. If you go onto Youtube and look up data consolidation, see
    http://www.youtube.com/watch?v=xmqTN0X-AgY

    Hope this helps.

    Andrew

+ 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