How to a string of texts that meets a certain criteria from a column

[Shankar5]

Hello,

Many thanks in advance for your guidance and help!

I have a column (Column L below) which has several texts in many rows. I would like Excel to search for the following text strings from this column (Table, Chair, Desks, Cabinets) and if any of these are found then return the same text string (Table, Chair, Desks or Cabinets). If not found then I would like Excel to return the text of 'Other items'. In any row in the column, only one of the text strings (Table, Chair, Desk or Cabinets) is likely to be found. I tried with IFError, Search, etc but did not make much progress

Once again sincerely appreciate your help!

Column L

NOTEBOOK,9 1/4"X7 1/4",BLACK

MARKER,SHARPIE,RETRACT,RED

FLYER,SPECIALTY & MACHINES

FLAG,POST-IT,ARROW,BRIGHT CLRS

BATTERY,COPPERTOP,AA

PEN,BALLPOINT,CLASSIC,MEDIUMBE

BATTERY,DURACELL ALKALINE,AA

DISINFECTING WIPES,LYSOL

CLIPS,ASSORTED,COLORED

FILE,EXPANDING,JAN-DEC,12PKT

POST-IT NOTES,5X8,RULED

FLAG,POST-IT,SIGN HERE

DAB N SEAL ENVELOPE MOISTENER

TAPE,ECO,MAGIC,19MMX15.2M

PRESTO LETTER OPENER,WHITE

BUSINESS CARD HOLDER,MESH,BK

FLYER,SPECIALTY & MACHINES

STAPLER,COMPANION DESK,BLACK

<tbody>
</tbody>

 

[revcanon]

you could try something like this:

    Sub test()
    
    Dim wb As Workbook
    Dim ws As Worksheet
    LastRow = Range("L" & Rows.Count).End(xlUp).Row
    Dim rnga As Range
    Dim rngb As Range
    Dim rngc As Range
    Dim rngd As Range
    Set ws = ActiveSheet

    
    Dim i As Integer
    For i = LastRow To 2 Step -1 'skips your header row
    Set rnga = Range("L" & i).Find("table", lookat:=xlPart)
    Set rngb = Range("L" & i).Find("chair", lookat:=xlPart)
    Set rngc = Range("L" & i).Find("desk", lookat:=xlPart)
    Set rngc = Range("L" & i).Find("cabinet", lookat:=xlPart)
    If Not rnga Is Nothing Then ws.Range("M" & i).Value = "Tables" Else GoTo 10
10:
    If Not rngb Is Nothing Then ws.Range("M" & i).Value = "Chairs" Else GoTo 20
20:
    If Not rngc Is Nothing Then ws.Range("M" & i).Value = "Desks" Else GoTo 30
30:
    If Not rngd Is Nothing Then ws.Range("M" & i).Value = "Cabinets" Else GoTo 40
40:
    If rnga Is Nothing And rngb Is Nothing And rngc Is Nothing And rngd Is Nothing Then _
    ws.Range("M" & i).Value = "Other Items"
                
    Next i


    End Sub

Please test on a copy.

 

[Adjunctor]

Here is another solution without VBA, using an array formula instead, I developed this one to find keywords on text strings and works just fine:

{=IFERROR(VLOOKUP(INDEX(kSearchs,MATCH(SMALL(IFERROR(SEARCH(kSearchs,L2),""),1),SEARCH(kSearchs,L2),0)),kSearchs,1,FALSE),"Other Items")}

Where kSearchs is a named range containing the your keywords list. I use a dynamic range so whenever I want to add keywords I just type them at the bottom of the list and the range adjust by itself, but you are OK using a fixed named range; if you don't want to use a named range just use the actual range on your formula.

IMPORTANT! Remember this is an array formula, don't type the brackets around it, instead press Ctrl-Shift + Enter on the first cell, I'm assuming your data has headers and the first formula should refer to L2, if not just adjust the cell reference. Once you enter the first formula just drag down and Excel will fill the rest of the cells.

Saludos!

 

[Shankar5]

Dear Revcanon and Adjunctor,

Many thanks for reverting promptly. Revcanon I would prefer to use a function/formula than a VBA code, unless everything fails.

Adjunctor - I defined a named range calling it ksearchs and included Table, Chair, Desks and Cabinets. (Ideally I would like to include a wildcard asterisk * with these text strings). However the value returned was only Other Items for all the cells and not those text strings (Tables, etc) where it occured. Any advise please Adjunctor?

you could try something like this:

    Sub test()
    
    Dim wb As Workbook
    Dim ws As Worksheet
    LastRow = Range("L" & Rows.Count).End(xlUp).Row
    Dim rnga As Range
    Dim rngb As Range
    Dim rngc As Range
    Dim rngd As Range
    Set ws = ActiveSheet

    
    Dim i As Integer
    For i = LastRow To 2 Step -1 'skips your header row
    Set rnga = Range("L" & i).Find("table", lookat:=xlPart)
    Set rngb = Range("L" & i).Find("chair", lookat:=xlPart)
    Set rngc = Range("L" & i).Find("desk", lookat:=xlPart)
    Set rngc = Range("L" & i).Find("cabinet", lookat:=xlPart)
    If Not rnga Is Nothing Then ws.Range("M" & i).Value = "Tables" Else GoTo 10
10:
    If Not rngb Is Nothing Then ws.Range("M" & i).Value = "Chairs" Else GoTo 20
20:
    If Not rngc Is Nothing Then ws.Range("M" & i).Value = "Desks" Else GoTo 30
30:
    If Not rngd Is Nothing Then ws.Range("M" & i).Value = "Cabinets" Else GoTo 40
40:
    If rnga Is Nothing And rngb Is Nothing And rngc Is Nothing And rngd Is Nothing Then _
    ws.Range("M" & i).Value = "Other Items"
                
    Next i


    End Sub

Please test on a copy.

 

[Adjunctor]

You don't need to include a wildcard with this formula. Your results suggest to me that you didn't entered the formula pressing Ctrl-Shift-Enter. Do you see brackets "{ }" surronding your formula?

 

[Shankar5]

You don't need to include a wildcard with this formula. Your results suggest to me that you didn't entered the formula pressing Ctrl-Shift-Enter. Do you see brackets "{ }" surronding your formula?

No brackets. It looks like as under: =IFERROR(VLOOKUP(INDEX(Ksearchs,MATCH(SMALL(IFERROR(SEARCH(Ksearchs,L2),""),1),SEARCH(Ksearchs,L2),0)),Ksearchs,1,FALSE),"Other Items") with Ksearchs being a defined name which points to a range of cells having the text Table, Chairs, Desks and Cabinets.

 

[Adjunctor]

OK, that means you did not enter it as an array formula, and that's why is not working. Press F2 to edit the formula and then just hit Enter <enter> while holding down Ctrl <ctrl>and Shift<shift></shift></ctrl></enter>

 

[Shankar5]

No luck! Still the same results.

 

[Adjunctor]

I just tested it and it's working. Now, with the sample data you are posting here all of the cells trow the same result (Other items) because none of the cells contains your keywords (Table, Chair, Desk, Cabinets).

I changed one of your rows to include a keyword and it did work, see image below (modified row is in blue font)

 

[Shankar5]

Thank you Sir! It worked. I just re-copied the whole thing. Thanks a Million!

I just tested it and it's working. Now, with the sample data you are posting here all of the cells trow the same result (Other items) because none of the cells contains your keywords (Table, Chair, Desk, Cabinets).

I changed one of your rows to include a keyword and it did work, see image below (modified row is in blue font)