search word in each cell

[G2K]

Hi all,


i have data sheet containing customer feedback, column H of this sheet contians feedback text. there are 4500 rows and i have to search 12 keywords in each cell(H2 t0 H4500). the key words are -

Unhappy
Declined
Dissatisfied
Frustrated
Frustrating
Unreasonable
Increase in margin
Competitor
Error
Bad
Charges
Mistake.

if any of these or more than one word found in respective cell then macro should return the text in Adjucent right cell.

thanks in advance

 

[Yard]

WIth your list of 12 keywords in A1:A12, try this to check H2

=IF(ISNA(LOOKUP(9.99999999999999E+307,SEARCH(" "&$A$1:$A$12&" "," "&$H2&" "),$A$1:$A$12)),"-",VLOOKUP(LOOKUP(9.99999999999999E+307,SEARCH(" "&$A$1:$A$12&" "," "&$H2&" "),$A$1:$A$12),$A$1:$B$12,2,0))

 

[ravishankar]

Hi
With keywords in col A (any number of words) try the following codes

Sub G2K()
Dim x As Long, y As Long, a As Long, b As Long
x = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
y = Sheets("sheet1").Cells(Rows.Count, 8).End(xlUp).Row
Sheets("sheet1").Range("I:I").ClearContents
    For a = 2 To y
        For b = 1 To x
            If InStr(Cells(a, 8), Cells(b, 1)) > 0 Then
            Cells(a, 9) = Cells(a, 9) & ", " & Cells(b, 1)
            End If
        Next b
        Cells(a, 9) = Right(Cells(a, 9), Len(Cells(a, 9)) - 1)
    Next a
MsgBox "Complete"
End Sub

matching terms will be in col I (9)
Ravi

 

[G2K]

thanks Yard/Ravi for your quick and correct Reply

formula is working absolutely fine

i am geting an error in below line of sub()

Cells(a, 9) = Right(Cells(a, 9), Len(Cells(a, 9)) - 1)

as given cell is blank. there is no data in Column I. we remove contentns of this column before we move to compare these string.

Please check.

thanks again

 

[G2K]

Posted By Ravi-

Hi
With keywords in col A (any number of words) try the following codes
Code:


Sub G2K()
Dim x As Long, y As Long, a As Long, b As Long
x = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
y = Sheets("sheet1").Cells(Rows.Count, 8).End(xlUp).Row
Sheets("sheet1").Range("I:I").ClearContents
For a = 2 To y
For b = 1 To x
If InStr(Cells(a, 8), Cells(b, 1)) > 0 Then
Cells(a, 9) = Cells(a, 9) & ", " & Cells(b, 1)
End If
Next b
Cells(a, 9) = Right(Cells(a, 9), Len(Cells(a, 9)) - 1)
Next a
MsgBox "Complete"End Sub
</PRE>
matching terms will be in col I (9)
Ravi



Thanks for your help,

i have made few changes in given code, and it's working perfactly fine now

For a = 2 To y
For b = 1 To x

If InStr(UCase(Cells(a, 8)), UCase(Cells(b, 1))) > 0 Then
Cells(a, 9) = Cells(a, 9) & ", " & Cells(b, 1)
End If
Next b

If (Len(Cells(a, 9)) > 0) Then
Cells(a, 9) = Right(Cells(a, 9), Len(Cells(a, 9)) - 2)
Else
Cells(a, 9) = "-"
End If
Next a

as instr was not able to find keywords in diffrent cases(lower/Upper/Regular).

thanks again guys for your kind help

Regards
G2K