to find selective words from a senetence

[khushboo#]

Hi All,


i have hard time figuring out a possible way to simplify my task which requires me to look through a range of data,(e.g, A1:A20), each cell contain a specific word. Look for that word into a range of B1: B10, each cell contains Sentence, if taht words are founds then output it as the word, which are find in that particular cell.
Attachment are for sample example. will it be possible to get the desired result through VBA, i was using formula for the same purpose.
Formula: =MAX(IF(ISERROR(FIND(A1:A20,B1)),-1,1)*ROW(A1:A20))

>>if senetence from range B, have 2 words founds from range A, then output should be the first word found


if done through VBA, will be best option to resolve this.

[jindon]

UDF
C2:

=GetWords(B2,$A$2:$A$21)
filldown.

Please Login or Register  to view this content.

[khushboo#]

Hi jindon,

Thanks for you reply, But can you please provide me little clarity on how can i use This Code.

[jindon]

......................

[khushboo#]

Hi,

Please check for the attached file, as its giving me output of some words, which are not in range data(Range A words)
E.G: Tommy


Thanks

[jindon]

Please check for the attached file, as its giving me output of some words, which are not in range data(Range A words)

What is it?

[mohan.r1980]

Hi Khushboo,

you are not clarifying If a word not found then what to do?

May be below code would be work

beacause of Jindon Sir i made below code (earlier he help me for finding words in array)

Please Login or Register  to view this content.

[FlameRetired]

Withdrawn by FR ... double posted.

[FlameRetired]

@ khushboo#

I'm confused. Which upload do you want solution for? Post #1 and Post #5 are not the same problem.

I have formula solution for post #1 if you are interested. It doesn't "fit" the other file though.

Array entered in C2 and filled down.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[khushboo#]

Hi mohan.r1980,


Can you attach a sample file with your code.

[mohan.r1980]

Hi Khushboo,

Please find attached file with code

[mohan.r1980]

hi khushboo#,

Please find updated file

[khushboo#]

Hi jindon,


As my post question has got its solution, and i am using code which are provided by you, but as i was looking for little more modification.
I want data from Column A to be in other sheet and output to be different sheet.
As numbers of data is reaching to 23500 number, so saving taht into a separate sheet will be really helpful.
Thanks in advance!

[jindon]

See the attached

Please Login or Register  to view this content.

[khushboo#]

Hi jindon,
Thanks a lot for Help.