Pivot Table, consolidating data

jamroll

New Member
Joined
Jan 30, 2014
Messages
2
Hi there,
I have a question regarding pivot tables, I think.

I have one sheet with multiple columns that have the same headings. What I'm trying to do is on another sheet, insert a table that consolidates all the Job numbers into one column, with the hours billed and hours worked as columns. Example table:


Job #Hours billedHours workedJob #Hours billedHours workedJob #Hours billedHours workedJob #Hours billedHours workedJob #Hours billedHours worked
1113211222113521145511524
11122112441131151142411577
181561111512183451843118555

<tbody>
</tbody>

So what I would like to do is have a table on Sheet2 that has 3 columns, Job #, Hours billed, Hours worked. The data will be constantly changing as the year goes on. Also, sometimes job numbers will be repeated, and I would like the totals to be consolidated. So, the table would look like this:

Job #Hours billedHours worked
1112016
11266
113167
11479
115911
18156
18345
18431
18555

<tbody>
</tbody>


Any ideas? Unfortunately this is the only way I can format it, as sometimes I work on more than one job number in a day, and the first column has to be date, and I can only have one row per date.

All the best,
jamroll.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

francoisdublin

New Member
Joined
Jan 23, 2014
Messages
32
If your source data was organised in this way i.e. repeating the date in the first column:

DateJob #Hours billedHours worked
Jan 111156
Jan 111232
Jan 211488

<tbody>
</tbody>

You would have a table which you can use in a Pivot to create both of the views in your question.

Otherwise, check out the Consolidate feature in Data menu/tab with the option to consolidate by label. Might work for you.
 

jamroll

New Member
Joined
Jan 30, 2014
Messages
2
Thanks for the response.
I've attached a more thorough table so you can see what I'm trying to achieve.
Column 1 is date.
Column 2 automatically counts the annual days worked. To do this so far I'm using the formula =IF(A3<>"",B2+1,"").

DateAnnual Days WorkedRegoTypeJob #Ticket #HoursMinsRegoTypeJob #Ticket #HoursMinsRegoTypeJob #Ticket #HoursMinsRegoTypeJob #Ticket #HoursMinsRegoTypeJob #Ticket #HoursMinsTotal HoursTotal Mins
1 jan1abc20611a23cba35022a34rst20533a44qwe12344a63asd09855a441918
5 jan2abc20611b2.13rst20533b749.17
14 jan3cba35077a3.44asd09855b34qwe12399a4610.414

<tbody>
</tbody>


If you can think of a way to do the automatic count for the annual days worked, even with multiple entries of the same date, I'm all ears!

Cheers.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,049
Members
414,357
Latest member
Gemma_R

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
Top