Tagging Words Using VBA in MS Excel

Iykeln

I am having trouble sorting this out. I have an excel worksheet. Column A of sheet1 contains about 300,000 tokens and the column A of sheet2 contains about 15, 000 tokens with possible part-of-speech tags spread on the same row with each token. Format example:

A in sheet1        (A            B    C    D     E) in sheet2
now                may           NN   MD
earth              but           CC   CJS
between            can           MD   NN
been               think         VB   VBP  VBZ  NN
think              now           NN   JJ   RB   IN
may                between       IN   CC   CJS
red                ...
between
now
think
may
now           
...

my task is to take each word in A(sheet 2), search for it in A(sheet1). If found, copy tags on its row and paste the tags in cells adjacent to all the occurrence of the words found in A(sheet1). That is, if think, between, etc. are found, then I will have this output in sheet1:

A          B     C    D   E
now               
earth 
between    IN   CC   CJS            
been               
think      VB   VBP  VBZ  NN          
may                
red               
between    IN   CC   CJS
now
think      VB   VBP  VBZ  NN
may
now           
...

I have this code that does only the searching of non empty cells in A(sheet1) and A(sheet2), check whether each word in A(sheet2) is in A(sheet1). If true, then will copy the word (instead of the associated tags) and paste it in cells in C columns adjacent to results in A(sheet1). Result looks like below instead of above.

A          B      C        D    E
now               
earth 
between          between            
been               
think            think         
may                
red               
between          between
now
think            think
may
now           
...

Here is the code:

Sub CopyDataToPlan()
Dim LData As String
Dim LData2 As String
Dim iVal As Integer
Dim iVal2 As Integer


iVal = Worksheets("Sheet1").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
iVal2 = Worksheets("Sheet2").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count

For LData_counter = 1 To iVal
    LData = Sheets("Sheet1").Cells(LData_counter, 1).Value

    For LData2_counter = 1 To iVal
        LData2 = Sheets("Sheet2").Cells(LData2_counter, 1).Value
        If (LData2 = LData) Then
            Sheets("Sheet1").Cells(LData_counter, 3) = LData2
            'Selection.Copy
        End If

    Next LData2_counter

Next LData_counter
End Sub

Also, this code when I try using it above 50,000 words, it either run and hang half-way or causes overflow. My aim: I prepared data in A(sheet1 & 2) for the purpose of tagging POS tags I developed to the words in A(sheet1). Words in A(sheet2) are unique words with most probable tags for it. Many users I employed for the task are more familiar with MS Office. So, I decided to make the job simpler for them. Instead of them going through the tags of about 100 and finding a tag for a word in A(sheet1), this system will provide them with a word and all possible tags for that word. All they will now do , is to take a word in A(sheet1), look through the tags provided and select one tag meant for it. Pls! I need help. Thanks guys!

Ron Rosenfeld

You could also do this with worksheet formulas. For example

Sheet1!B1:  =IF(LEN(IFERROR(VLOOKUP($A1,Sheet2!$A$1:$E$15000,COLUMNS($A:B),FALSE),""))=0,"",VLOOKUP($A1,Sheet2!$A$1:$E$15000,COLUMNS($A:B),FALSE))

Fill right to E1; then select B1:E1 and fill down to row 300000

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Split words using VBA in Excel cells

From Dev

Using MS Access to run code in excel vba

From Dev

Delete empty rows using VBA - MS Excel

From Dev

Using Excel VBA to SQL delete in MS Access

From Dev

MS Project to Excel Gantt Chart using VBA

From Dev

Using MS Access to run code in excel vba

From Dev

Pasting cell range from MS Excel into MS PowerPoint using VBA

From Dev

MS Excel: How to insert words in cells using Find and Replace

From Dev

Automatically connect to ms excel and check for unread emails using excel vba

From Dev

Searching an excel with two search words (multiple lookup) using vba macro

From Dev

VBA scripting for MS excel

From Dev

Find a value from a range in MS Excel using VBA

From Dev

Using excel as inputfile in vba (scripting - non MS OFFICE)

From Dev

MS Project to Excel Gantt Chart using VBA Follow On

From Dev

Using Tasks to Add Excel Data with Same ID to MS Project - VBA

From Dev

Query MS Access using Excel VBA, SQL BETWEEN dates query

From Dev

Highlighting searched words on Vba excel

From Dev

Highlighting searched words on Vba excel

From Dev

Excel VBA extract words from string

From Java

How to define the number of columns to be exported from excel to MS access table using access VBA?

From Dev

MS Excel 2010 - Calculation using Abs() stops working when placed in other VBA

From Dev

Import data from different webpages in one in MS Excel sheet using VBA

From Dev

From ms access table how to paste required data form array (getrows) to excel specific ranges using vba

From Dev

Retrieve specific cell value from multiple excel files in ms access form textbox using vba

From Dev

Ms Excel VBA type mismatch error

From Dev

MS Excel VBA find cell with string value

From Dev

Hide MS VBA screen in Excel 2010

From Dev

Output MS Access Query to Excel with vba

From Dev

how to apply a VBA to multiple rows in MS excel?

Related Related

  1. 1

    Split words using VBA in Excel cells

  2. 2

    Using MS Access to run code in excel vba

  3. 3

    Delete empty rows using VBA - MS Excel

  4. 4

    Using Excel VBA to SQL delete in MS Access

  5. 5

    MS Project to Excel Gantt Chart using VBA

  6. 6

    Using MS Access to run code in excel vba

  7. 7

    Pasting cell range from MS Excel into MS PowerPoint using VBA

  8. 8

    MS Excel: How to insert words in cells using Find and Replace

  9. 9

    Automatically connect to ms excel and check for unread emails using excel vba

  10. 10

    Searching an excel with two search words (multiple lookup) using vba macro

  11. 11

    VBA scripting for MS excel

  12. 12

    Find a value from a range in MS Excel using VBA

  13. 13

    Using excel as inputfile in vba (scripting - non MS OFFICE)

  14. 14

    MS Project to Excel Gantt Chart using VBA Follow On

  15. 15

    Using Tasks to Add Excel Data with Same ID to MS Project - VBA

  16. 16

    Query MS Access using Excel VBA, SQL BETWEEN dates query

  17. 17

    Highlighting searched words on Vba excel

  18. 18

    Highlighting searched words on Vba excel

  19. 19

    Excel VBA extract words from string

  20. 20

    How to define the number of columns to be exported from excel to MS access table using access VBA?

  21. 21

    MS Excel 2010 - Calculation using Abs() stops working when placed in other VBA

  22. 22

    Import data from different webpages in one in MS Excel sheet using VBA

  23. 23

    From ms access table how to paste required data form array (getrows) to excel specific ranges using vba

  24. 24

    Retrieve specific cell value from multiple excel files in ms access form textbox using vba

  25. 25

    Ms Excel VBA type mismatch error

  26. 26

    MS Excel VBA find cell with string value

  27. 27

    Hide MS VBA screen in Excel 2010

  28. 28

    Output MS Access Query to Excel with vba

  29. 29

    how to apply a VBA to multiple rows in MS excel?

HotTag

Archive