Data Correlation

[Justin37]

Below, I have pasted a screenshot of the workbook that I have created in Excel. It contains things that have occured in the past 24 hours, all within five minute increments. I would like a code that would search through every row labeled DIRs and find cells that contain strings. Then, search the Alerts row and find any strings that occured 36 hours before the given DIR? It would basically correlate any alerts that happened within 36 hours of a DIR. I know that is a lot to ask, but anything that you could come up with would be very helpful. Thank you!

15-Oct

Time

0:00

0:05

0:10

0:15

0:20

0:25

0:30

0:35

0:40

0:45

0:50

0:55

1:00

1:05

1:10

1:15

1:20

1:25

1:30

1:35

1:40

1:45

1:50

1:55

2:00

2:05

2:10

2:15

2:20

2:25

2:30

2:35

2:40

2:45

2:50

2:55

3:00

3:05

3:10

3:15

3:20

3:25

3:30

3:35

3:40

3:45

3:50

3:55

4:00

4:05

4:10

4:15

4:20

4:25

4:30

4:35

4:40

4:45

4:50

4:55

5:00

5:05

5:10

5:15

Alerts

Guard Availibility Level I, Target Type 0-2 Range Level II

DIRs

Maint.

16-Oct

Time

0:00

0:05

0:10

0:15

0:20

0:25

0:30

0:35

0:40

0:45

0:50

0:55

1:00

1:05

1:10

1:15

1:20

1:25

1:30

1:35

1:40

1:45

1:50

1:55

2:00

2:05

2:10

2:15

2:20

2:25

2:30

2:35

2:40

2:45

2:50

2:55

3:00

3:05

3:10

3:15

3:20

3:25

3:30

3:35

3:40

3:45

3:50

3:55

4:00

4:05

4:10

4:15

4:20

4:25

4:30

4:35

4:40

4:45

4:50

4:55

5:00

5:05

5:10

5:15

Alerts

PTU- Warm up Oscillator current error, Azimuth Accuracy Level II, Elevation Accuracy Level II

PHM to RAMS Interface Failure, RCSP to PHM Interface Failure, 5 Axis - (+X/+Y) CBIT Error, 5 Axis - (-X/+Y) CBIT Error, 5 Axis - (+X/-Y) CBIT Error, 5 Axis - (-X/-Y) CBIT Error, 5 Axis - Azimuth Actuator CBIT Error, 1 Axis - Elevation CBIT Error, RCSP Ethernet 2 Interface Failure, Single Axis - Elevation Fault, Five Axis - Rotation Fault

PHM to RAMS Interface Failure, RCSP to PHM Interface Failure, PTU - Warm up Oscillator current error, 5 Axis - (+X/+Y) CBIT Error, 5 Axis - (-X/+Y) CBIT Error, 5 Axis - (+X/-Y) CBIT Error, 5 Axis - (-X/-Y) CBIT Error, 5 Axis - Azimuth Actuator CBIT Error, 1 Axis - Elevation CBIT Error, RCSP Ethernet 2 Interface Failure, Azimuth Accuracy Level II, Elevation Accuracy Level II, Single Axis - Elevation Fault, Five Axis - Rotation Fault

DIRs

System fell to Standby

Maint.

17-Oct

Time

0:00

0:05

0:10

0:15

0:20

0:25

0:30

0:35

0:40

0:45

0:50

0:55

1:00

1:05

1:10

1:15

1:20

1:25

1:30

1:35

1:40

1:45

1:50

1:55

2:00

2:05

2:10

2:15

2:20

2:25

2:30

2:35

2:40

2:45

2:50

2:55

3:00

3:05

3:10

3:15

3:20

3:25

3:30

3:35

3:40

3:45

3:50

3:55

4:00

4:05

4:10

4:15

4:20

4:25

4:30

4:35

4:40

4:45

4:50

4:55

5:00

5:05

5:10

5:15

Alerts

Guard Availability Level I, Azimuth Accuracy Level II, Elevation Accuracy Level II, Target Type 0-2 Range Level II

DIRs

Maint.

<colgroup><col style="width: 92pt; mso-width-source: userset; mso-width-alt: 4498;" width="123"> <col style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;" width="86"> <col style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;" width="77"> <col style="width: 68pt; mso-width-source: userset; mso-width-alt: 3328;" width="91"> <col style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;" width="81"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;" width="79"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" width="76"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;" width="79"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" width="76"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;" width="82"> <col style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;" span="3" width="77"> <col style="width: 68pt; mso-width-source: userset; mso-width-alt: 3328;" width="91"> <col style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;" width="81"> <col style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;" width="95"> <col style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;" width="85"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;" width="83"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" width="76"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;" width="83"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" width="76"> <col style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;" width="86"> <col style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;" width="80"> <col style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;" width="81"> <col style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;" width="80"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;" width="79"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" width="76"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;" width="83"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" span="6" width="76"> <col style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;" width="77"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" width="76"> <col style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;" width="77"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;" width="79"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" width="76"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;" width="83"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" span="9" width="76"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;" width="82"> <col style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;" width="77"> <col style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;" width="86"> <col style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;" width="80"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" width="76"> <col style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;" width="77"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" span="2" width="76"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;" width="78"> <col style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;" width="84"> <col style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;" width="77"> <col style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;" width="84"> <col style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;" span="2" width="77"> <col style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;" width="81"> <col style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;" width="80"> <tbody>
</tbody>

 

[Justin37]

I forgot to add that I'm using Excel 2007. I was thinking maybe there would be a way to do this using the rgb codes for the colors. Such as, when a red cell ("bad") is found, find all yellow (neutral) cells within 36 hours of red. Again, thanks.

 

[StephenCrump]

One possible approach:

1. Use conditional formatting to colour red any non-blank "DIR" cell.

2. Use VBA and Worksheet_SelectionChange event: If the user selects a red (non-blank "DIR") cell, use VBA to colour yellow any non-blank "Alert" cells within the previous 36 hours.

However, given you're working in 5 minute increments, that means 288+ columns to scan for colours .... I'd be inclined to have a separate dashboard summary area and have VBA summarise all the non-blank "Alert" cells for any particular selected red cell?

 

[Justin37]

Here is a link to my file.

https://www.dropbox.com/sh/1wsne201krmdc4d/hxZ5PMSaPX

 

[Justin37]

I'm new to vba, but I will give that a try.

 

[Justin37]

That sounds like a good idea. Is there a way to list the alerts that occur within 36 hours of a DIR on a separate worksheet? That way the DIRs and the alerts are all on one page and easily viewable? Sorry, new to this. Just started learning vba yesterday.

 

[Justin37]

https://www.dropbox.com/s/f7wei2chrc183qc/DIRS_ALERTS.xlsx

This is updated file.

 

[StephenCrump]

Thanks

Most things are possible with VBA. I think the issue here is what makes sense for you from a dashboard management point of view ...

From the file you first posted, I could see that your summary sheet was derived from a larger data set for DIRs, specifiying DIR number, type, priority, date, start time, end time, description, action taken etc etc.

Your "Data Correlation" sheet summarises these DIRs in five minute increments. You've done this manually for the moment. A formula approach would be possible, but I'd argue that you need a more succinct dashboard than a sheet that's 288+ columns wide.

My guess is that the dashboard should be able to answer questions like:
- How many Category X DIRs did we get last week?
- How many unresolved DIRs do we have at the moment?
- How many DIRs do we get between 12mn and 6am?

It should be relatively simple to set up a dashboard sheet that answers these sort of questions by querying the raw data, rather than an intermediate summary sheet?

In relation to correlating DIRs with Alerts, it should also be relatively simple to list all Alerts within 36 hours prior to DIR commencement. But again, this potentially generates a lot of information to be shown on a dashboard.

- Presumably there is detailed raw data available for the Alerts, i.e. rather than just what's currently manually entered in your "Data Correlation" sheet?
- Is it possible to do some preliminary correlation using formulae, and only show relevant Alerts, e.g. does an Alert type 1 correspond to a DIR type 1?

I suggest the next step might be to attach another file with simplified dummy data for DIRs and Alerts?