macro extracting text

[Tomek]

Hello,

I have a problem to write a macro as below:
I have one column with long text and there are several thousands rows.
I have also a second column with about fifty rows with different words or
short three- words sentences in it.
I would like to have a macro that searches the first column for the same
words or short sentence as in the second column. If the word or the short
sentence is found in the first column , it is copied and pasted to the
third column.

Thank you very much in advance for help.

Tomek

 

[Tom Ogilvy]

Assume the first column is A1:A5000
second coumn is B1:B50

Sub CheckWords()
dim cell as Range, rw as Long
rw = 1
for each cell in Range("B1:B50")
if application.countif(A1:A5000,"*" & cell.Value & "*") > 0 then
cells(rw,3).Value = cell.Value
rw = rw + 1
end if
Next
End Sub

 

[Tomek]

Dnia Mon, 31 Jan 2005 15:21:01 -0500, Tom Ogilvy napisa³(a):

Assume the first column is A1:A5000
second coumn is B1:B50

Sub CheckWords()
dim cell as Range, rw as Long
rw = 1
for each cell in Range("B1:B50")
if application.countif(A1:A5000,"*" & cell.Value & "*") > 0 then
cells(rw,3).Value = cell.Value
rw = rw + 1
end if
Next
End Sub

Something is wrong because when I try it I get a news " mismatch type"

Tomek

 

[Tom Ogilvy]

typo/omission

if application.countif(A1:A5000,"*" & cell.Value & "*") > 0 then
should be

if application.countif(Range("A1:A5000"), _
"*" & cell.Value & "*") > 0 then

 

[Tomek]

Dnia Tue, 1 Feb 2005 23:01:46 -0500, Tom Ogilvy napisa³(a):

typo/omission

if application.countif(A1:A5000,"*" & cell.Value & "*") > 0 then
should be

if application.countif(Range("A1:A5000"), _
"*" & cell.Value & "*") > 0 then

now it works but unfortunately it is not what I would like it to be.

I would like to have a macro that searches the first column for the same
words or short sentence as in the second column. If the word or the short
sentence is found in the first column , it is copied and pasted to the
third column.

so the third column should be filled with some words or short sentences but
when I run the macro it filled only several cells in third column but I
would like to have something like below:

A(words) B(conditions) C (results of macro)
aaaa aa aa
bbb bb bb
cccc ee nothing
aa aa
bb bb
fff nothing
cccc nothing
ggg nothing
aaa aa
ee ee

I am sorry very much for not explaining it clear enough.

Tomek

 

[Tom Ogilvy]

Try this
in C2 put in a formula

=if(B2="","",if(countif(A2,"*"&B2&"*")>0,B2,""))

then drag fill down column C.

 

[Tomek]

Dnia Wed, 2 Feb 2005 14:26:41 -0500, Tom Ogilvy napisa³(a):

=if(B2="","",if(countif(A2,"*"&B2&"*")>0,B2,""))

ALMOST , ALMOST but it shows correct result only when columns A and B
have the same string in the same row.
But the main idea is that the formula should search whole column B whether
one of the string is the same as in A column. If it is , it should be
copied to C column.

EXAMPLE:

A B C(RESULT)

ZZ AA ZZ(because ZZ is in column B
DD BB DD(because DD is in column B
GG CC GG(because GG is in column B
FF DD FF(because FF is in column B
FF EE FF(because FF is in column B
GG FF GG(because GG is in column B
ZZ GG ZZ(because ZZ is in column B
AA ZZ AA(because AA is in column B
HH NOTHING (because there isn't HH in column B
ZT NOTHING(because there isn't ZT in column B
MH NOTHING (because there isn't MH in column B
AA AA(because AA is in column B
ZM NOTHING(because there isn't ZM in column B
DD DD (because there is DD in column B

I hope it is clearer now.

Thank you very much for your patience.

Tomek