Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Consolidating two different types of data Days and Cost into data table suitable for upload to database

  1. #1
    New Member
    Join Date
    Dec 2010
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Consolidating two different types of data Days and Cost into data table suitable for upload to database

    Hi all,

    I am grateful for any help at all with this.

    I have a pricing template for a project I am working on. On one spreadsheet tab that shows my resources loaded costs, bill rates and days. I have several pricing tabs like this.

    The problem I have is I want to be able to create a dataset on a separate tab which is a datatable that I can collate the information from. I have tried using multiple consolidation ranges pivottable but got a bit lost...
    Name Bill Code Loaded Cost Bill Rates Rate card Jan-14 Feb-14 Mar-14 TOTAL Jan-14 Feb-14 Mar-14 TOTAL Jan-14 Feb-14 Mar-14 TOTAL
    Michael 35 20 50 Germany 23 23 23 69 460 460 460 1380 1150 1150 1150 3450
    John 36 40 50 France 20 1 12 33 800 40 480 1320 1000 50 600 1650
    Graham 78 80 90 UK 12 15 19 46 960 1200 1520 3680 1080 1350 1710 4140
    TOTAL 55 39 54 148 2220 1700 2460 6380 3230 2550 3460 9240

    The desired datatable I am looking for is the below and would like to know the optimum automated way of doing this and if I am better using links or pivots. Ideally pivots, so if rows are inserted it doesnt cause an issue.
    Name Bill Code Loaded Cost Rate Bill Rates Month Days Loaded Cost Rates
    Michael 35 20 50 Jan-14 23 460 1150
    Michael 35 20 50 Feb-14 23 460 1150
    Michael 35 20 50 Mar-14 23 460 1150

    If you can help, you are a life saver

    Thanks and Regards,

    Nick

  2. #2
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,756
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Consolidating two different types of data Days and Cost into data table suitable for upload to database

    Hi Nick,

    The process you describe is sometimes called to UnPivot, Reverse Pivot, Transform, or Normalize data.
    You can find many ways to do this on the web using those key words.

    Approaches include:
    VBA Macro
    Using a PivotTable with Multiple Consolidation Ranges
    SQL Query
    Excel 2013, using PowerPivot and Power View
    Excel 2010, using PowerPivot and Data Explorer Add-Ins

    The best choice for you will depend on what version of Excel you are using, your familiarity with adapting VBA or SQL.

    Here's a few links to get you started....
    Spreadsheet Page Excel Tips: Creating A Database Table From A Summary Table
    Transpose or Unpivot Entire Datasets with Data Explorer Bacon Bits
    Daily Dose of Excel Blog Archive UnPivot Shootout
    Using Excel 2016

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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