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!
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.
Comments