Re: Is there any way I can search within each cell value of column A for all possibilities

[abhisheksingh]

Re: Is there any way I can search within each cell value of column A for all possibilities

I have following type of data in Column A.

Column A:

slonp213d Logical Disk Free Space is low on Drive D: 9% [Consumer Marketing - ODS Production]
scola32m-msgs alert in BB [Testserver IBM Cognos Impromptu Express 9.0]
SHODP193DSQL: Windows Service Stopped [SAP]
SUDNA61B: A HP Health Collection is in failed state. (Domain Controller)
vlonj017w Logical Disk Fragmentation Level is high [HUB 3 (VRS, Offerings hub, RightsHub CSI)]
Scola63m: msgs alert in BigBrother (BGSA Citrix)
scola72t Userenv -msgs alert in BB [test Database server]
Vlonj019w Application Pool disabled due to worker process failure [HUB 3 (VRS, Offerings hub, DDR, CSI)]
slonp46d Dirty Shutdown Tracker [Remedy Live]
slonp40d Microsoft.SQLServer.2000.AgentJob.JobDuration [GSS]
SLONP157DSQL2 Windows Service Stopped [Catalog (IRIS)]
scola72t Msgs alert in BB [test Database server]
Ssyd10: Free Disk Space in GB for C: is 11 GB, threshold value for this monitor is 12 GB [Virtual Center Server]
sudna05f msgs alert in BB [File Server Uden ]
SNYCD91E Logical Disk Free Space is low C: 9% [Exchange Services]
VLONJ009W Health Service Heartbeat Failure (Hub Web Server)
SUDNA61B: Dirty Shutdown Tracker (Domain Controller)
SCOLA102V: CPU Alert in BigBrother (ESX Cologne Prod Server 2)
scola72t: msgs alert in BigBrother (test Database server)
SCOLA45B: Web Application - https://partnerlogin.emihub.com (GADEX - DC 1 Cologne)
VLONJ009W: Health Service Heartbeat Failure (Hub Web Server)

I want to print a specific part of the each cell in column B. Like for the first cell, I want only "Logical Disk Free Space is low" to be printed in column B. However, for the 2nd cell of column A, I want only "msgs alert in BB" this part to be printed in second cell of column B. I want some specific part to be printed in Column B if the certain text string is found within the each cell value of column A. Nothing is fixed except the values I am looking for within Column A cell. I have a long list of values to be checked within Like shown below:

Column A:

slonp213d Logical Disk Free Space is low on Drive D: 9% [Consumer Marketing - ODS Production]
scola32m-msgs alert in BB [Testserver IBM Cognos Impromptu Express 9.0]
SHODP193DSQL: Windows Service Stopped [SAP]
SUDNA61B: A HP Health Collection is in failed state. (Domain Controller)
vlonj017w Logical Disk Fragmentation Level is high [HUB 3 (VRS, Offerings hub, RightsHub CSI)]
Scola63m: msgs alert in BB (BGSA Citrix)


Column B:

Logical Disk Free Space is low
msgs alert in BB
Windows Service Stopped
A HP Health Collection is in failed state.
Logical Disk Fragmentation Level is high



Is there any way I can search within each cell value of column A for all possibilities of Column B and if anything matches that to be printed in Column B???

Like a formula which looks for the string “Logical Disk Free Space is low” in first cell of Column A, if it is not found then looks for next string of Column B which is “msgs alert in BB” in first cell of Column A and it repeats this action for the number of values in Column B.
If it is possible with VB script, please let me know the script.

I need help ASAP. I have attached file with Data.

 

[JLGWhiz]

Re: Is there any way I can search within each cell value of column A for all possibilities

You can use VBA to search for text in a column by using either the Find or the InStr functions. However, you must be able to define what those text strings are by one of the following means:
1. A list of the different strings to search in a specific range on a worksheet, or in an array.
2. A logical parameter such as always being located between a colon and a comma, or between two key words, but something that is consitent in each cell where the string would appear.
3. A pattern, similar to 2. above but applying to the string itself (This would be my least preferred method)
4. A User Interface through use of an Input devic, which could be InputBox, TextBox, Label, etc. where the user enters the string to search manually on each iteration. (This could also be used to build an array or list first, then use one of those as the search criteria source)
I might have missed a method, but these are the most common and practical in my opinion. If you cannot get one of these to fit, then it will probably have to be done with sorting, filtering or just plain old manual effort.