Macro to select multiple contains from another sheet

[suyogpat]

Hi,

I am stuck here with below query.

I have one excel data sheet1 column "E" I have data and in "sheet2" in column A I have some keywords. I want a macro to copy the keyword from sheet2 and search that keyword in sheet1 (contains) and next to the same I need macro to write "exclude". Now I think I can do that with a loop as in sheet2 I have around 10 keywords which in future will increased.

So is there any way by which macro will automatically select the keyword from sheet2 until blank column and search all the criteria in "Sheet1" and give me the output.

Sorry if i missed anything. Do let me know if its sounds confusing..

Thanks in advance for your time and efforts.

*sorry not able to add worksheet as its not allowed *

 

[JackDanIce]

Try:

Sub M1()

    Dim x       As Long
    Dim LR      As Long
    Dim arr()   As Variant
    Dim dic     As Object

    Set dic = CreateObject("Scripting.Dictionary")
    
    With Sheets("Sheet2")
        LR = .Cells(.Rows.count, 1).End(xlUp).row
        arr = .Cells(1, 1).Resize(LR).Value
        For x = LBound(arr, 1) To UBound(arr, 1)
            dic(arr(x, 1)) = "exclude"
        Next x
    End With
    
    With Sheets("Sheet1")
        LR = .Cells(.Rows.count, 5).End(xlUp).row
        .Cells(1, 6).Resize(LR).ClearContents
        arr = .Cells(1, 5).Resize(LR, 2).Value
        For x = LBound(arr, 1) To UBound(arr, 1)
            arr(x, 2) = dic(arr(x, 1))
        Next x
        .Cells(1, 5).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    End With
    
    Erase arr
    Set dic = Nothing
    
End Sub

 

[My Aswer Is This]

Try this:

Sub My_Answer()
Application.ScreenUpdating = False
Dim i As Long
Dim x As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets(1).Cells(Rows.Count, "E").End(xlUp).Row
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 1 To Lastrow
        For x = 1 To Lastrowa
            If Sheets(1).Cells(i, "E").Value = Sheets(2).Cells(x, 1).Value Then
                Sheets(1).Cells(i, "F").Value = "exclude"
            End If
        Next
    Next
Application.ScreenUpdating = True
End Sub

 

[JackDanIce]

@M.A.I.T although not required, even if using more than one loop, it helps with readability to include the counter specific to the Next statement being closed off:
https://stackoverflow.com/questions...nclude-the-variables-name-in-a-next-statement

Else, similar to why use a string letter as variable for the column index number inside cells. If you're taking that approach for readability's sake consistency suggests to apply same for Loops and the Next counter being specific.

E.g.

For i = 1 to Lastrow
  For x = 1 to Lastrowa
    'code
 Next x
Next i

 

[suyogpat]

Hi JackDanIce,
Thank you for quick reply and helping me out I tried this code but here what is happening I have around 5000 rows and macro is running only in first cell and then its stopping here is what I have with me
This is my excel sheet looks like with around 5k+data..

Column A of Sheet 1
OMD USA INC
DHL Solutions Retail GmbH
FIA CARD ASSOCIATes
WIPRO LIMITED
COGNIZANT Bank TECHNOLOGY SOLUTIONS terminology US
NAV NEMZETI city VAMHIVATAL
RADIAL INC
SD VZW
THE BARTECH GROUP INC
LEVI PLAZA TIC

My Criteria in Sheet2 of column E2
Bank
City
State

So I have pasted your code here but what is happening her is macro is running only in F1 and then it stops. What I need is macro to run the code for whole 5000+rows and it should search those criteria from sheet 2 and paste exclude in front of those cells in sheet1.

Thanks a lot for your time and help. Really appreciate your work

 

[JackDanIce]

Hi suyogpat,

This part of the code uses column A on Sheet2 to determine the last used row in that column. If the column is blank, it returns a value of 1, which may be why F1 is only cell updated:

With Sheets("Sheet2")
        LR = .Cells(.Rows.count, 1).End(xlUp).row
       arr = .Cells(1, 1).Resize(LR).Value
       For x = LBound(arr, 1) To UBound(arr, 1)
            dic(arr(x, 1)) = "exclude"
       Next x
End With

Change the 1 to 5 if you want to base the last used row on column E (i.e. column A is column 1, column B is column 2, column C is column 3 etc)

 

[suyogpat]

Hi JackDanIce,

Sorry for troubling you again. I am very new to macro so facing issue here. I tried the suggestion you asked but no luck. Now I am not getting even exclude in F1. This is how I need the final data. so like in row 3 bank word is there so it should identify and put "exclude" in output same with row 5 identified city and type exclude.

Thanks a lot in advance. Sorry again for asking repeatedly but I am still in learning phase of macro.

My Sheet 1 in excel
[TABLE="class: grid, width: 80"]
<tbody>[TR]
[TD][TABLE="width: 255"]
<tbody>[TR]
[TD="align: left"]Submitted Merchant Name[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]OMD USA INC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DHL Solutions Retail GmbH[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WIPRO bank LIMITED[/TD]
[TD]Exclude[/TD]
[/TR]
[TR]
[TD]COGNIZANT TECHNOLOGY SOLUTIO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FIA CITY SERVICES Trade NATIONAL [/TD]
[TD]Exclude[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[JackDanIce]

In Sheet1, try the following formula, starting in F1:

=IF(ISNUMBER(MATCH(E1,Sheet2!$A$1:$A$5000,0)),"Exclude","")

And drag to the last row in Sheet1 for column F.

If that works, record a macro that does the same and post the resulting code.

If that doesn't work, record a macro that shows how you would perform the exercise manually, it may be some details are being lost or not considered.