Component Life Cycle Management, How to Extract Correct Data

Hey Guys,

After some excellent previous Assistance I have now been tasked with further Excel Work. I'll try to be as precise as possible.

I am trying to create a life-cycle management sheet that is simple for the user but gives specific data.

The user would input "Date", "Machine type", "Component Set No.", and "Component". This would happen over a period of time and build up a picture of how often each component was lasting and it's average life in days.

I am fine from the user input side but am struggling to figure out how to extract ONLY the data I need to display in a graph.

For example: If I want to see the average no. of days "component X" is lasting before being replaced I've not been able to extract just that data.
I have tried using a pivot chart but this won't do exactly what I'm after.

If you have the time please see the attached for a clear explanation.
Component LifeCycle Example.xlsx

Thanks in anticipation.

John.

Hi

Have a look at the attached. I've just dummied up some data so that there is a reasonable volume. Put your selections into the range F5:J5. There are 2 "helper" columns: E and F. E brings out the date of matching items, and F will calculate the days between the changes.

919397.xlsx

HTH

rylo

Hey Rylo,

Thanks So much for all your time on this!

Really Close. Only issue now is when I change selection dates the first number/date that column F picks out is always = to the corresponding column E meaning that the number of days is showing thousands.
componentLifeCycle IMG.jpg


I really appreciate your help!

Thanks again,
John.

Hi

OK, change this formula and copy down.

F7: =IF(AND(A7>=$F$5,A7<=$G$5,B7=$H$5,C7=$I$5,D7=$J$5,COUNT($E$7:E7)>1),A7-MAX($E6:E$7),"")

I noticed this time when playing that you can get some silly results if there are no or only 1 instance of a item. Don't know how you want to handle those sorts of things.

rylo

Rylo,
You're a legend - Worked a treat! Not too worried about the 0 or 1 instances. I can work with that.

Thanks Once again!