CODEWITHSUNDEEP
excelexcel-formula
Covfefe
Covfefe

Reputation: 11

excel search list for item and its suffixes

I am trying to search for all instances in a list for entries which match or begin with those listed in a criteria group. Imagine a list of servers consisting of the below. Where we have a database on that server also, it is indicated with an _SQL, _ORACLE suffix:

Column A

I need to search for all servers from the group below

Column D

I need to be able to identify the 2 listings of VM002 and 3 listings of VM003 in the list in Column A.

I can easily search for how many times VM002 or VM003 appear in the overall list by using the function, =COUNTIF(A:A,D1&"*").

What I need then is some way of indicating which servers in Col A match those of the smaller group, so I can sort, filter, etc. How can I identify entries in Col A along with their suffixes?

Upvotes: 0

Views: 262

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

First enter the following User Defined Function (UDF) in a standard module:

Public Function Keyword(r1 As Range, r2 As Range) As Variant
    v1 = r1.Text
    For Each r In r2
        If InStr(v1, r.Text) = 1 Then
            Keyword = 1
            Exit Function
        End If
    Next r
    Keyword = ""
End Function

Then in B1 enter:

=keyword(A1,$D$1:$D$2)

and copy downward:

enter image description here

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

EDIT#1:

To use the UDF for a full column, use this version instead:

Public Function Keyword(r1 As Range, r2 As Range) As Variant
    v1 = r1.Text

    Set r2 = Intersect(r2, r2.Parent.UsedRange)

    For Each r In r2
        If r.Value <> "" Then
            If InStr(v1, r.Text) = 1 Then
                Keyword = 1
                Exit Function
            End If
        End If
    Next r
    Keyword = ""
End Function

Upvotes: 1

Related Questions