Stop word removal script

[JCTalk]

Hiya,

I use a frequency generator that generates a list of words used in the titles of an increasing number of support cases. I need to remove a list of user defined 'stop words' that do not relate to IT to enable me to analyse the data easier.

The best way forward I think would be to select a start cell, then run the macro which would check down the list of unique words found in the titles against a list of 'stop words' located in a tab called 'Stop Words' in column A. Any stop words found in the list should be removed.

Can anyone help me by providing the VBA to perform this?

Many thanks

 

[Deepak]

Can we have the sample pls.

 

[JCTalk]

Can we have the sample pls.

Of course. My apologies.

Many thanks.

 

[Deepak]

In simple way:

You want to remove Sheet Stop Words col A data from sheet Sheet1

 

[Marc L]

Hi !​

​

According to the attachment :​

Sub Macro1()
            Application.ScreenUpdating = False
With Sheet1
    .Rows(1).Insert xlDown
    .Cells(1).Value = "Word"
    .[C2].Formula = "=ISNUMBER(MATCH(A2," & Sheet2.Cells(1).CurrentRegion.Address(External:=True) & ",0))"
    .Cells(1).CurrentRegion.AdvancedFilter xlFilterInPlace, .[C1:C2]
    .[C2].Clear
    .[_FilterDatabase].Delete xlShiftUp
    .ShowAllData
    .Activate:  ActiveWindow.ScrollRow = 1
End With
            Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[JCTalk]

In simple way:

You want to remove Sheet Stop Words col A data from sheet Sheet1

Thats correct Deepak.

 

[JCTalk]

Thank you Marc L. I'll give that a try and report back.

EDIT: Hi Marc. That worked great thank you.

 

[JCTalk]

Hi Marc L,

I've copied the marco across to my workbook and it doesn't run. I can only assume thats because I have many other tabs in the workbook.

What code would need to be changed to refer to a specific tab when checking the Stop Word list (The name of the tab is StopWords)?

The main page doesn't need to be referenced. It should be ActiveSheet. Needs to run on the page that's currently selected.

Thanks Marc L.

 

[Marc L]

Proof that it's a bad idea to attach a dummy workbook
which is not the exact reflect of the real one !

Instead of CodeName Sheet1 use ActiveSheet.

You could replace Sheet2 by Worksheets("StopWords")
but it's safer to work with CodeName in case of a worksheet renamed.

Other way is moving the code to the worksheet module using Me instead …
(In fact if there is no worksheet reference before Cells,
so it's the worksheet where module is, no really need Me. before …)
​

 

[janeriany]

hi,
i wanna make prepocessing step using VBA macro, when i finished tokenizing step my data, i stucked in stopword removal step. how if i want remove stopword columns A-P data from sheet 2? this's my example. :D
sheet1 is stopword list
many thanks