Pivot Table, consolidating data

[jamroll]

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 billed	Hours worked	Job #	Hours billed	Hours worked	Job #	Hours billed	Hours worked	Job #	Hours billed	Hours worked	Job #	Hours billed	Hours worked
111	3	2	112	2	2	113	5	2	114	5	5	115	2	4
111	2	2	112	4	4	113	11	5	114	2	4	115	7	7
181	5	6	111	15	12	183	4	5	184	3	1	185	5	5

<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 billed	Hours worked
111	20	16
112	6	6
113	16	7
114	7	9
115	9	11
181	5	6
183	4	5
184	3	1
185	5	5

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

 

[francoisdublin]

If your source data was organised in this way i.e. repeating the date in the first column:

Date	Job #	Hours billed	Hours worked
Jan 1	111	5	6
Jan 1	112	3	2
Jan 2	114	8	8

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

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,"").

Date	Annual Days Worked	Rego	Type	Job #	Ticket #	Hours	Mins	Rego	Type	Job #	Ticket #	Hours	Mins	Rego	Type	Job #	Ticket #	Hours	Mins	Rego	Type	Job #	Ticket #	Hours	Mins	Rego	Type	Job #	Ticket #	Hours	Mins	Total Hours	Total Mins
1 jan	1	abc	206	1	1a	2	3	cba	350	2	2a	3	4	rst	205	3	3a	4	4	qwe	123	4	4a	6	3	asd	098	5	5a	4	4	19	18
5 jan	2	abc	206	1	1b	2.1	3	rst	205	3	3b	7	4																			9.1	7
14 jan	3	cba	350	7	7a	3.4	4	asd	098	5	5b	3	4	qwe	123	9	9a	4	6													10.4	14

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