Data Correlation

Justin37

New Member
Joined
Jan 6, 2014
Messages
10
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:000:050:100:150:200:250:300:350:400:450:500:551:001:051:101:151:201:251:301:351:401:451:501:552:002:052:102:152:202:252:302:352:402:452:502:553:003:053:103:153:203:253:303:353:403:453:503:554:004:054:104:154:204:254:304:354:404:454:504:555:005:055:105:15
AlertsGuard Availibility Level I, Target Type 0-2 Range Level II
DIRs
Maint.
16-Oct
Time 0:000:050:100:150:200:250:300:350:400:450:500:551:001:051:101:151:201:251:301:351:401:451:501:552:002:052:102:152:202:252:302:352:402:452:502:553:003:053:103:153:203:253:303:353:403:453:503:554:004:054:104:154:204:254:304:354:404:454:504:555:005:055:105: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:000:050:100:150:200:250:300:350:400:450:500:551:001:051:101:151:201:251:301:351:401:451:501:552:002:052:102:152:202:252:302:352:402:452:502:553:003:053:103:153:203:253:303:353:403:453:503:554:004:054:104:154:204:254:304:354:404:454:504:555:005:055:105: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>
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Justin37

New Member
Joined
Jan 6, 2014
Messages
10
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,192
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 6, 2014
Messages
10

ADVERTISEMENT

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

Justin37

New Member
Joined
Jan 6, 2014
Messages
10
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.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,192
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,137,292
Messages
5,680,648
Members
419,923
Latest member
Kalthus

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
Top