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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,637
Messages
5,838,497
Members
430,551
Latest member
digitalbrolly

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