General advice required on how to approach a problem

[Rhothgar]

Good evening,

I have been with a friend today who wants to analyse the efficiency of his staff. He owns an automotive garage.

One of his staff is pretty good with Excel and has created a spreadsheet that will work out the effiency on any particular day.

We extracted a CSV file from the software he uses to invoice jobs and this basically shows how many hours have been booked to a job in any one day.

I want to be able to help him get historical data quickly.

The CSV which has in the region of 9000 rows for staff and how much time they invoiced on any particular day. The CSV (now saved as an XLS) has ranges of information for each member of staff. I have sorted it into date order per member of staff.

I want to somehow search the ex-CSV file for staff initials then date and reference that into the other worksheet.

I am open to advice on how to best approach this.

Extract from CSV:

In this spreadsheet, we have:-

Col B. - Technician's Initials
Col. G - Date
Col H - Hours Invoiced

Extract from Efficiencies Sheet 1

This spreadsheet is very rough.

What his guy is trying to do is show the days of March 2014 in Row 1 and the hours available for booking out on jobs under Available and what has actually been achieved under the Charged cells.

This needs extending backwards to take account of different staff and hours charged since December 2011. He's not particularly bothered about going back that far but if it is simply a case of dragging cells to extend correct equations then it does no harm having accurate information going back further.

Extract from Efficiencies Sheet 2

I have checked his guy's equations for these efficiencies as I jsut want to get the principle right first and then collate the information.

So, to summarise in essence, I want to be able to lookup the date and technician's initials from the CSV extract (bearing in mind that any new CSV can be exported at any time) and then sum the hours from Col. H for a set date and then put that total sum of hours under Charged for the correct date in the Efficiencies spreadsheet.

Any advice will be gratefully accepted.

I am probably intermediate on Excel 2003. I do not like 2007 upwards so never made the change. Old habits die hard. I appreciate I may need the power of some Excel 2007 or later to achieve this.

My friend has it on his garage computer but I would prefer to do as much legwork in the comfort of my own home where possible.

 

[shawnhet]

My advice would be to play around with Pivot tables - they are an excellent way of summarizing a bunch of data and allow you to reformat it easily to present different views.

PivotTable reports 101 - Excel - Office.com

If you don't want to try pivot tables - the SUMPRODUCT function should work well for Excel 2003.

Use SUMPRODUCT in Excel 97-2003 to Summarize Worksheet Data

Cheers,

 

[Rhothgar]

Many thanks for your suggestions, shawnhet.

Sometimes you don't know what question to ask the Help in Excel!

 

[Rhothgar]

I am up at my friends at the moment and he is on Excel 2007.

The spreadsheet I created in Excel 2003 opens in Compatibility Mode and cannot see the data from which the pivot data was created.

Also when create a sum in a cell below, I cannot drag and extend.It reverts back to a specific Technicians initials. It seems locked.