RepairShopr reports manipulation

[Kalemaroni]

Hi, I'm new here so please forgive me if I'm asking in the wrong place or anything!

For my IT support / computer / phone repair business I use a CRM package called RepairShopr which outputs many different types of reports for my jobs into Excel and I'm trying to manipulate the data in such a way that it can give me the reports I need.

The one I'd first like to do is work out what the average time spent on a particular job type. I think once I have that worked out it will be a basis for me to do pretty much any type of report I can think of.

Now, I'm not looking to be completely spoon-fed, although if someone were to write the exact formula/vba script in their reply I would be flabbergasted and eternally grateful! What I'm looking for though, is to know which direction I should be going - pure VBA or is it possible to do it purely with formulas, or is a combination of the two the most efficient and least labour intensive?

Below is the image of two different RepairShopr report outputs.

The Time sheet shows all job tickets and the Duration column shows the timer logs. You can start/stop timers on multiple tickets at the same time, hence there are multiple of the same ticket number in the ticket column.

The Tickets sheet shows all the tickets in the system and the problem_type column is what I want to work with.

I want to compile a list of each Problem_Type and the average Duration that Problem_Type takes per ticket. So what I'm imagining I have to do is first get the total Duration on all tickets of one problem_type then divide it by the number of unique tickets that are of that problem_type.

Any hints to the right direction or links to similar issues would be really appreciated.

 

[Kalemaroni]

Sorry, just to clarify the second last line should read:

I want EXCEL to compile a list of each Problem_Type and the average Duration that Problem_Type takes per ticket. So what I'm imagining I have to do is first get the total Duration on all tickets of one problem_type then divide it by the number of unique tickets that are of that problem_type.

 

[Doctor_Puffet]

I have written a series of macros to extract RepairShoppr data to Excel via the RepairShopper API.
LEt me know which ones you need and I will post them here.