Failure rate after service

[ZoNe747]

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!

 

[Andrew Poulsom]

Welcome to MrExcel.

What do you want to sum in E1 exactly?

 

[ZoNe747]

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!

 

[Andrew Poulsom]

What's in the list you want to lookup and where is it?

 

[ZoNe747]

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.

 

[Andrew Poulsom]

Please post a sample of the data on Sheet2.

 

[ZoNe747]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Machine Number[/TD]
[TD]Date Of Break Down[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]1232345[/TD]
[TD]01/01/1999[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]213213[/TD]
[TD]21/03/2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]5544[/TD]
[TD]01/09/2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 4[/TD]
[TD]6563232[/TD]
[TD]18/08/2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
This is what would be Sheet 2.

 

[Andrew Poulsom]

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?

 

[ZoNe747]

[TABLE="width: 686"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Customer[/TD]
[TD]Time Of Service[/TD]
[TD]Machine[/TD]
[TD]Time Break down Occurred[/TD]
[TD]Days Between Failure and Service[/TD]
[TD]Type Of Work[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD="align: right"]10/01/2014 13:07[/TD]
[TD="align: right"]12080592[/TD]
[TD] [/TD]
[TD="align: right"]-41649.55[/TD]
[TD]Service[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD="align: right"]16/01/2014 10:15[/TD]
[TD="align: right"]12261707[/TD]
[TD] [/TD]
[TD="align: right"]-41655.43[/TD]
[TD]Service[/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD="align: right"]20/01/2014 07:38[/TD]
[TD="align: right"]7182[/TD]
[TD] [/TD]
[TD="align: right"]-41659.32[/TD]
[TD]Service[/TD]
[/TR]
[TR]
[TD]Customer 4[/TD]
[TD="align: right"]20/01/2014 11:47[/TD]
[TD="align: right"]12292522[/TD]
[TD] [/TD]
[TD="align: right"]-41659.49[/TD]
[TD]Service[/TD]
[/TR]
[TR]
[TD]Customer 5[/TD]
[TD="align: right"]20/01/2014 13:13[/TD]
[TD="align: right"]13023123[/TD]
[TD] [/TD]
[TD="align: right"]-41659.55[/TD]
[TD]Service[/TD]
[/TR]
[TR]
[TD]Customer 6[/TD]
[TD="align: right"]21/01/2014 15:31[/TD]
[TD="align: right"]12261739[/TD]
[TD] [/TD]
[TD="align: right"]-41660.65[/TD]
[TD]Service[/TD]
[/TR]
[TR]
[TD]Customer 7[/TD]
[TD="align: right"]22/01/2014 14:58[/TD]
[TD="align: right"]12282266[/TD]
[TD] [/TD]
[TD="align: right"]-41661.62[/TD]
[TD]Service[/TD]
[/TR]
[TR]
[TD]Customer 8[/TD]
[TD="align: right"]29/01/2014 12:25[/TD]
[TD="align: right"]12352954[/TD]
[TD] [/TD]
[TD="align: right"]-41668.52[/TD]
[TD]Service[/TD]
[/TR]
[TR]
[TD]Customer 9[/TD]
[TD="align: right"]30/01/2014 12:10[/TD]
[TD="align: right"]11348205[/TD]
[TD] [/TD]
[TD="align: right"]-41669.51[/TD]
[TD]Service[/TD]
[/TR]
[TR]
[TD]Customer 10[/TD]
[TD="align: right"]31/01/2014 15:47[/TD]
[TD="align: right"]11478915[/TD]
[TD] [/TD]
[TD="align: right"]-41670.66[/TD]
[TD]Service[/TD]
[/TR]
[TR]
[TD]Customer 11[/TD]
[TD="align: right"]03/02/2014 11:49[/TD]
[TD="align: right"]11509372[/TD]
[TD] [/TD]
[TD="align: right"]-41673.49[/TD]
[TD]Service[/TD]
[/TR]
[TR]
[TD]Customer 12[/TD]
[TD="align: right"]04/02/2014 16:35[/TD]
[TD="align: right"]11207350[/TD]
[TD] [/TD]
[TD="align: right"]-41674.69[/TD]
[TD]Service[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Andrew Poulsom]

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))