RepairShopr reports manipulation

Kalemaroni

New Member
Joined
Sep 15, 2020
Messages
9
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.

scrnshot.jpg
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Kalemaroni

New Member
Joined
Sep 15, 2020
Messages
9
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Oct 23, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,241
Messages
5,595,017
Members
413,960
Latest member
ikkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top