Failure rate after service

ZoNe747

New Member
Joined
Sep 1, 2014
Messages
8
Good morning,

I have been asked to create a report which looks at when a machine has a call come in after a service. so for example it would need to look like:

A1: Site Name
B1: machine number
C1: Date of last service
D1: the date of the next failure from a selected list.
E1: an auto sum that I will put in once D1 is completed.

The problem is a Vlookup wont cut it at there are over 50 thousand fields and I am not sure where to begin. My knowledge in VBA is starting to grow but I need more practice.

Any help you can give would be amazing!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Thank you for the welcome!

the autosum I would use after I get the dates from D1. With that I would then be able to count number of days between failure. The issue is how to get that information in D1 to appear. We have around 3500 machines which are serviced and around 50,000 calls in our system. I think the solution is using VBA but I wanted the advice from the experts!
 
Upvote 0
Right that would help wouldn't it sorry first post failings on my part :)

so Sheet1 would contain the main page where I would have the above information A:E

A1: Site Name
B1: machine number
C1: Date of last service
D1: the date of the next failure from a selected list.
E1: an auto sum that I will put in once D1 is completed.

Sheet2 would then contain the historical data for the site of all breakdown calls. I would need a formula or VBAcode to compare the machine number and date of last service from Sheet1 with that of the Machine Number and date of a breakdown on Sheet2. It would need to return the first date which occurs after the date of last service. The problem is that there are would be calls before the service so a VLookup for just the machine number will not work.

 
Upvote 0
CustomerMachine NumberDate Of Break Down
Customer 1123234501/01/1999
Customer 221321321/03/2014
Customer 3554401/09/2014
Customer 4656323218/08/2014

<tbody>
</tbody>
This is what would be Sheet 2.
 
Upvote 0
I'm still not sure what you want to sum. Is it the number of breakdowns for that machine between the dates in C1 and D1?
 
Upvote 0
CustomerTime Of ServiceMachineTime Break down OccurredDays Between Failure and ServiceType Of Work
Customer 110/01/2014 13:0712080592 -41649.55Service
Customer 216/01/2014 10:1512261707 -41655.43Service
Customer 320/01/2014 07:387182 -41659.32Service
Customer 420/01/2014 11:4712292522 -41659.49Service
Customer 520/01/2014 13:1313023123 -41659.55Service
Customer 621/01/2014 15:3112261739 -41660.65Service
Customer 722/01/2014 14:5812282266 -41661.62Service
Customer 829/01/2014 12:2512352954 -41668.52Service
Customer 930/01/2014 12:1011348205 -41669.51Service
Customer 1031/01/2014 15:4711478915 -41670.66Service
Customer 1103/02/2014 11:4911509372 -41673.49Service
Customer 1204/02/2014 16:3511207350 -41674.69Service

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Does this help more? This is my main page on sheet one. Its not about the sum of how many days but actually getting the information from Sheet2. I need a way of identifying the machine number and date on sheet 1 whilst checking sheet 2. Once it identifies the machine number it will check if the date is before or after to service date. If before it will keep searching until it finds one that is after the service date. Once it has found a call that has occured after service it will return the date value on Sheet1.
 
Upvote 0
Does this work for you in D2 copied down?

=INDEX(Sheet2!C:C,MATCH(1,INDEX((Sheet2!B:B=C2)*(Sheet2!C:C>B2),),FALSE))
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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
Back
Top