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