Compare List Of Text Stings

jasbir · Nov 3, 2014

Hello everyone - i am hoping someone can please help as this is a manual task so far and I am hoping it can be automated via formula or vba? I have a list of text search strings terms that I need to search in several columns for possible matches(hits). If match is found for any words on the search string then I would like to display them in a new COL so I can filter and take further actions. Here is my example below. Thank you.

List to search on	Search Strings	Hit
Laptop must be retuned for repairs	laptop or desktop or "mobile device" or "Hard Drive" or "back up" or remove or removal or add or delete or monitor or build or repair or built	laptop, repair
Remove old ID from hard drive	laptop or desktop or "mobile device" or "Hard Drive" or "back up" or remove or removal or add or delete or monitor or build or repair or built	remove, hard drive
Bran07 was on Monitor List for remo	laptop or desktop or "mobile device" or "Hard Drive" or "back up" or remove or removal or add or delete or monitor or build or repair or built	Monitor, Removal
Dfat007 is on build report	laptop or desktop or "mobile device" or "Hard Drive" or "back up" or remove or removal or add or delete or monitor or build or repair or built	build
mobile device failur	laptop or desktop or "mobile device" or "Hard Drive" or "back up" or remove or removal or add or delete or monitor or build or repair or built	mobile device
wind2007/x4/4tran	laptop or desktop or "mobile device" or "Hard Drive" or "back up" or remove or removal or add or delete or monitor or build or repair or built	No Match

jasbir · Nov 3, 2014

I failed to mention that I have been searching google for days this and have come up empty so far. So far I have this formula which detects if one of the word on the list is present but only return a numerical number ("1") not actual matching word. =IF(COUNT(SEARCH({"Replace", laptop, desktop, etc,,F30)),1,"") - not what I need but hoping someone can help. Thanks.

Aladin Akyurek · Nov 3, 2014

Create a range of search strings, select the range, and name the selection Keywords.

Let column A house the target strings.

In B2 enter and copy down:

=LOOKUP(9.999999999999999E+307,SEARCH(" "&Keywords&" "," "&$A2&" "),Keywords)

This will give you the last hit from Keywords if any.

shg · Nov 3, 2014

If a user-defined function is in your comfort zone,

Row\Col	A	B	C
1		add
2		back up
3		build
4		built
5		delete
6		desktop
7		Hard Drive
8		laptop
9		mobile device
10		monitor
11		removal
12		remove
13		repair
14
15
16	Laptop must be returned for repairs	laptop	B16: {=CatIf(ISNUMBER(SEARCH(" " & $B$1:$B$13 & " ", " " & A16 & " ")), $B$1:$B$13, ", ")}
17	Remove old ID from hard drive	Hard Drive, remove
18	Bran07 was on Monitor List for remo	monitor
19	Dfat007 is on build report	build
20	mobile device failure	mobile device
21	wind2007/x4/4tran

Code:

Function CatIf(avbIf As Variant, _
               rInp As Range, _
               Optional sSep As String = ",", _
               Optional bCatEmpty As Boolean = False) As String
    ' shg 2007
    ' UDF only
    
    ' Catenates the elements of rInp separated by sSep where the corresponding
    ' element of avbIf is True. Empty cells ignored unless bCatEmpty is True.

    Dim iRow        As Long
    Dim iCol        As Long
    Dim i           As Long

    On Error Resume Next
    i = UBound(avbIf, 2)

    If Err.Number Then
        ' avbIf is 1D
        For iRow = 1 To rInp.Rows.Count
            For iCol = 1 To rInp.Columns.Count
                i = i + 1
                If avbIf(i) Then
                    If bCatEmpty Or Not IsEmpty(rInp(iRow, iCol).Value2) Then
                        CatIf = CatIf & rInp(iRow, iCol).Value2 & sSep
                    End If
                End If
            Next iCol
        Next iRow
    Else
        ' it's 2D
        For iRow = 1 To rInp.Rows.Count
            For iCol = 1 To rInp.Columns.Count
                If avbIf(iRow, iCol) Then
                    If bCatEmpty Or Not IsEmpty(rInp(iRow, iCol).Value2) Then
                        CatIf = CatIf & rInp(iRow, iCol).Value2 & sSep
                    End If
                End If
            Next iCol
        Next iRow
    End If

    If Len(CatIf) Then CatIf = Left(CatIf, Len(CatIf) - Len(sSep))
End Function

jasbir · Nov 4, 2014

Aladin and Shg - thank you both for your quick responses. Shg - your solution works but I cannot save worksheets as macro enabled so I have to depend on non-macro based solution. Aladin- I could not make your formula work, I get an error "#Name?". I have COL=Keywords/COLB=formula and COLC=target strings to search on. Please advise. Thanks.

Keywords
laptop or desktop or "mobile device" or "Hard Drive" or "back up" or remove or removal or add or delete or monitor or build or repair or built	#NAME?	Laptop must be retuned for repairs

Aladin Akyurek · Nov 4, 2014

jasbir said:
Aladin and Shg - thank you both for your quick responses. Shg - your solution works but I cannot save worksheets as macro enabled so I have to depend on non-macro based solution. Aladin- I could not make your formula work, I get an error "#Name?". I have COL=Keywords/COLB=formula and COLC=target strings to search on. Please advise. Thanks.

Keywords
laptop or desktop or "mobile device" or "Hard Drive" or "back up" or remove or removal or add or delete or monitor or build or repair or built #NAME? Laptop must be retuned for repairs

<tbody>
</tbody>

Enter one by one all of the search strings in column A of the sheet you would name as Admin.
Select the range in A of Admin and name the selection via de Name Box as Keywords.

Now you are set to invoke the formula I proposed.

jasbir · Nov 4, 2014

Aladin- sorry but I am not an excel expert and still get same error. Can you please give me a working example? So far I have followed your steps: Sheet name is Admin. COL A contains list of search strings and header name is "Keywords". Your formula is in B2 and COL C contains the target search column.

COLUMN(A)	COLUMN(B)	COLUMN(C)
Keywords	Formula and Returned Matching Word(s)	target search list
add	#NAME?	Laptop must be returned for repairs
back up	Result should be for this line: Remove, Hard Drive	Remove old ID from hard drive
build		Bran07 was on Monitor List for remo
built		Dfat007 is on build report
delete		mobile device failure
desktop		wind2007/x4/4tran
Hard Drive
laptop
mobile device
monitor
removal
remove
repair

Aladin Akyurek · Nov 4, 2014

jasbir said:
Aladin- sorry but I am not an excel expert and still get same error. Can you please give me a working example? So far I have followed your steps: Sheet name is Admin. COL A contains list of search strings and header name is "Keywords". Your formula is in B2 and COL C contains the target search column.
[...]

If done properly, the suggested formula, i.e.,

=LOOKUP(9.99999999999999E+307,SEARCH(" "&Keywords&" "," "&$C2&" "),Keywords)

yields the following results...

Keywords	RESULT	target search list
add	laptop	Laptop must be returned for repairs
back up	remove	Remove old ID from hard drive
build	monitor	Bran07 was on Monitor List for remo
built	build	Dfat007 is on build report
delete	mobile device	mobile device failure
desktop	#N/A	wind2007/x4/4tran
Hard Drive
laptop
mobile device
monitor
removal
remove
repair

As you see, if successful, the formula yields one outcome per target strings. If you want all of the keywords figuring in a target string, we need a bit different approach.

jasbir · Nov 4, 2014

Aladin- I still cannot get this to work. I copied your formula to Cell C2 and still get "#Name?” which is strange as this is working on your side. I used Shift+Ctrl+Enter and pasted as: C2={LOOKUP(9.99999999999999E+307,SEARCH(" "&Keywords&" "," "&$C2&" "),Keywords)}
Also the matching search term could be anywhere in the sentence (these are ticket descriptions) and ideally we need to pick up ALL matching terms. For table below, cell B3 should show “remove” and “Hard Drive” .

	A	B	C
1	Keywords	RESULT	target search list
2	Remove	laptop	Laptop must be returned for repairs
3	Hard Drive	remove	Remove old ID from hard drive

Jasbir

Aladin Akyurek · Nov 4, 2014

jasbir said:
Aladin- I still cannot get this to work. I copied your formula to Cell C2 and still get "#Name?” which is strange as this is working on your side. I used Shift+Ctrl+Enter and pasted as: C2={LOOKUP(9.99999999999999E+307,SEARCH(" "&Keywords&" "," "&$C2&" "),Keywords)}
Also the matching search term could be anywhere in the sentence (these are ticket descriptions) and ideally we need to pick up ALL matching terms. For table below, cell B3 should show “remove” and “Hard Drive” .

A
B
C

1
Keywords
RESULT
target search list

2
Remove
laptop
Laptop must be returned for repairs

3
Hard Drive
remove
Remove old ID from hard drive

<TBODY>
</TBODY>

Jasbir

Read a bit closely the instructions.

1. Select A2:A14.
2. Go to the Name Box, type Keywords, and hit enter.
3. In B2 just enter and copy down (NO control+shift+enter)...

=LOOKUP(9.99999999999999E+307,SEARCH(" "&Keywords&" "," "&$C2&" "),Keywords)

Compare List Of Text Stings

jasbir

New Member

Excel Facts

jasbir

New Member

Aladin Akyurek

MrExcel MVP

shg

MrExcel MVP

jasbir

New Member

Aladin Akyurek

MrExcel MVP

jasbir

New Member

Aladin Akyurek

MrExcel MVP

jasbir

New Member

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

Compare List Of Text Stings

New Member

Excel Facts

New Member

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock