Solved

Search for string in Excel column with many rows.

Posted on 2016-10-01
10
57 Views
Last Modified: 2016-10-03
I have an Excel spreadsheet with over 23500+ rows that I would like to search column "B" for certain strings.

If one of the strings are found that I'm looking for,  then the word "present' would be put in the next column "C" in the same row.

These are the strings I need to look for.
They can either be lower or higher case letters:
re-start
restart
hold
ice
kill
terminate
inactive
success
force
start


Thanks
test.xlsx
0
Comment
Question by:rkckjk
  • 5
  • 4
10 Comments
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41825096
I have listed the criteria on Sheet2 in the range A1:A10 then used the following Array Formula (which requires confirmation with Ctrl+Shift+Enter instead of Enter alone)

On Sheet1
In C1
=IF(SUM(COUNTIF(B1,"* "&Sheet2!$A$1:$A$10&" *")),"Present","")

Open in new window

Confirm with Ctrl+Shift+Enter and then copy down.

For more details, refer to the attached.
test-1.xlsx
0
 
LVL 2

Author Comment

by:rkckjk
ID: 41825109
HI it looks like it worked but I added another row with the string "ICE" and it didn't find the string in that cell.

I'm attaching the Excel sheet you had sent back with the additional row.
test-1--1-.xlsx
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41825193
I have tweaked the formula, please refer to the attached.
test-1--1-.xlsx
0
 
LVL 2

Author Comment

by:rkckjk
ID: 41825250
It seems now to insert present when there's no string present that are listed in sheet 2 tab.

I've added 5 rows where you'll see it hit on, but it shouldn't have.

This is with your new formula.

Thanks
test-1--1-.xlsx
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41825267
Please find the attached macro-enabled file in which I have placed a user defined function called IsStringFound which requires two argument, cell to match and criteria range to match.
In the column D, you will find the following function in D1 which is copied down.
=IsStringFound(B1,Sheet2!$A$1:$A$10)

Open in new window

test-1--1--1.xlsm
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Author Comment

by:rkckjk
ID: 41825270
Not sure I understand how this is all supposed to work. Am I supposed to use the earlier formula and the function in column D?


I just tried to use the new formula or function by itself and it didn't work.
0
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41825273
No you can remove the previous formula from column C.
You will need to place the codes on VBA Module and then only this user defined function will work.
So if you are trying this formula in your original workbook, follow the following steps...

1) Open your workbook and press Alt+F11 to open VB Editor.
2) On VB Editor --> Insert --> Module --> And paste the code given below into the opened code window.
3) Close the VB Editor.
4) Save your file as Macro-Enabled Workbook and you are ready to use the function IsStringFound.

Function IsStringFound(RngToMatch As Range, Criteria As Range) As String
Dim x, str() As String
Dim i As Long, j As Long, cnt As Long
x = Criteria.Value
str() = Split(RngToMatch, " ")
For i = 0 To UBound(str)
   For j = 1 To UBound(x, 1)
      If str(i) <> "" And LCase(GetString(str(i))) = LCase(x(j, 1)) Then
         IsStringFound = "Present"
         Exit Function
      End If
   Next j
Next i
If cnt > 0 Then IsStringFound = "Present"
End Function

Function GetString(str As String) As String
Dim RE As Object, Matches As Object
Set RE = CreateObject("vbscript.regexp")
With RE
   .IgnoreCase = True
   .Pattern = "[A-Z]{1,}"
End With
Set Matches = RE.Execute(str)
If Matches.Count > 0 Then
   GetString = Matches(0)
End If
End Function

Open in new window

0
 
LVL 2

Author Closing Comment

by:rkckjk
ID: 41825316
Thanks
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41825444
You're welcome.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41826419
If you want to find those specific words, then it is worth adding a space before and after each word. Otherwise, it will find all words that contain the string eg looking for "force" will also find "forceful" and "enforce", "kill" will also find "skill" and "killing".

"Re-start" and "Restart" are probably surplus, you already have "start" which occurs in both.

Thanks
Rob H

EDIT: Just spotted Neeraj made this point in your follow on question.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now