Hi

Wondering if someone can help with excel. I have a huge timesheet upload that I’m trying to group by job number and trade but so far have not been able to get the pivot tables to work and I’m not keen on using vlookups etc as this conversion needs to be fully automatic and there are hundreds of job numbers, plus I need to get the data well formatted to upload it into ms project so headings or totals can be a pain.

Any ideas? Thanks in advance

Pivot Table is your answer...

What version of excel are you using ?

In Excel 2010, all you do is click on a cell in your data and excel will recognise all data which is not separated by a blank line horizontally or a blank cell in the headings.
Then click on Insert, Pivot Table
This will create a new sheet and on that sheet, just select the job field (you'll need to put a heading in your data called "JOB" in Cell A1.

Add the fields JOB and HOURS to your pivot table, and then change the HOURS maths to SUM (it will always default to COUNT)

Any problems, email me the sheet and I'll set it up for you.

Thanks

Yeah I had another look at the pivot table, didn't realise there were some hidden options on the labels in the pivot table so I could get the JOB number next to each discipline line, now can apply this to the timesheet data fine and see no issues now. Though I did it seems have to dublicate the job number so it was not just a sort field.

Cheers



[Edited on 25/3/15 by Mr Whippy]