Find / Search Function

jonnyp138

Board Regular
Joined
May 2, 2015
Messages
50
Good evening all, I am hoping someone can help me out, I have a report to get out for Monday and I am struggling on one section, its a bit hard to explain but I will do my best:

I have a report of all error messages that come from a servers event log in a spreadsheet represented in a column called "Brief Description" and I have created a new column and called Issue and I have to write a function that will extract the issue from the event message, an example of some of the events and their respective issue are as follows:

Message Issue
Server101- The Virtual Servers Processor Performance is at 94.25% : WARNING Processor
Server143 - Total CPU Usage is at 54.24% CPU
Server152 - Networking Functions monitoring is CRITICAL Networking Functions
Active Directory FSMO Role LDAP connections allowed check for Domain Naming Master monitored from SERVER423 is in state CRITICAL - Alert generated if FSMO role not accepting LDAP connections | LDAP Connections

Server01 - Logical Disk I: has 11.03 Available Space (%) Disk
Hope that makes sense.
Thanks in advance
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

jonnyp138

Board Regular
Joined
May 2, 2015
Messages
50
Ok this may make this a little easier, in another table I have a list of the most common issues such as:

This is a new table
Operating System
NT Service
Windows Events |
SQL Server
Disk
CPU
Processor
Bandwidth Utilization
System Uptime
Windows Event Log Monitor
LDAP connections
Unable to Ping device
Connectivity has returned
Memory Performance
Windows Service
Windows monitoring
Parameter Connect Status
vserverTable Container
Networking Functions
patrol
Memory usage
VirtualCenter
Free Memory
Windows 2003 monitoring
Hardware
Port
The Server has been restarted
communication
Filesystem Capacity

So what I would like to do is search the message ^^Examples above and in the message the first "keyword" it comes across that is in this keyword table should appear in the issues column
 

MarcelBeug

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
My advice would be to create a table, let’s say table “tabIssues”, with 2 fields:
Field “String” with strings to look for in the error messages, and
Field “Issue” with categories.

Examples:
String “Networking” with Issue “Networking Functions”
String “CPU” with issue “CPU”.

If your error messages start in A2, then enter in B2 array formula (confirm with CTRL-SHIFT-ENTER, not just ENTER) and copy down:
Code:
=INDEX(tabIssues[Issue],SMALL(IF(ISNUMBER(FIND(tabIssues[String],A2)),ROW(INDIRECT("1:"&COUNTA(tabIssues[Issue])))),1))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

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