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

njcduffy

New Member
Joined
Dec 14, 2010
Messages
1
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

<tbody>
</tbody>

<tbody>
</tbody>

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

<tbody>
</tbody>

<tbody>
</tbody>

If you can help, you are a life saver

Thanks and Regards,

Nick
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top