I do monitoring of some infrastructure for a private company. specifically, my job is to move around and identify non-functioning system along a defined route. all these systems have unique identification numbers.
a typical report, submitted at the end of the month looks the attached file. the comma (,) separating the figures simply indicates that several faults occurred along the same cubicle. for instance B3 means faults occurred on system numbered 20,23 and 25 and all these system take feed from the same cubicle.
several of such faults occur in a month. my job is to find out the regularity of such faults and assess the response of the company to resolving the faults. faults are deemed chronic
when the same report is filed by us for three successive days. so, for instance, if faults are reported on systems 20,23 and 25 for three days or more, the fault is chronic. the fault is still considered chronic if one of these figures (either 20 or 23 or 25) is a fault detected in three days of monitoring.
For now, I crudely have to scan through the entire worksheet and manually pick out these chronic faults and highlight them with colour.
How do i sort out, determine, pick out these chronic faults using excel functions. I'd welcome suggestions on other ways of analysing this same data.
I want the gurus to handle this for me
The attached file may help you a little, but first one or 2 observations.
Format the whole of your data capture sheet as Text, not General. Some entries have 1-20 which I guess means cubicles 1 to 20 inclusive, and others have 1--64.
I would imagine that you are using the double hyphen to stop Excel converting your entry to a Date.
If the cells are pre-formatted as Text, then you don't need to do this.
My code looks for any double hyphens and converts them to single hyphen.
Also, where you have entries like 1-20,40 would it be possible for you to always enter the data as 40,1-20.
In other words, make the range come after any single entries.
If so, then the code in the attached workbook, will extract data to a second sheet, with a row for each cubicle, which gets populated with an "x" if it appears on sheet1.
Conditional formatting then shows where there have been 3 or more days with consecutive "X"'s for the cubicle.
I hope that this will get you started.
Microsoft Excel MVP