Find list of words from sheet2 in sheet1 before a comma and extract text vba

[satish78]

Hi Friends,

Trying to find the solution on my task. But did not find suitable one to the need.
Here is my query and sample file with details.

Need a formula to find list of words before a (,) comma from sheet2A:A in Sheet1A:A(there is no data range like A1:a10) and extract text before the match
Formula should out put results like shown in ColumnD
ColumnC has found words from sheet2A:A in sheet1A:A

Hightail Spaces

Hightail Spaces lets you send unlimited files, of any size, to anyone. Collaborate with your team and evolve your work.

spaces.hightail.com

 

[Peter_SSs]

If you are talking about@ juddaaaa post xl2bb.xlam table.

How could I have been referring to that post when my questions were asked before that post had been made ?

1. How did you get the list of words in column C? Please explain the logic.

2. This indicates that you are using vba but in post 1 you said "Need a formula .." What are you looking for, formula or vba?

3. Is the last row of your expected results a mistake? If not, why does it end with "for"?

 

[satish78]

How could I have been referring to that post when my questions were asked before that post had been made ?

Sorry, Peter. The new UI of Mrexcel.com is different. I was able to read only 1st line of your first query.

Here are the answers.

1. How did you get the list of words in column C? Please explain the logic.
A. If you download the sample file in this first post of mine. You will see list of words in Sheet2A:A.

2. This indicates that you are using vba but in post 1 you said "Need a formula .." What are you looking for, formula or vba?
A. I need Formula or VBA. Anything which does the job.

3. Is the last row of your expected results a mistake? If not, why does it end with "for"?
A. Yes, you are right. It have to be "the" instead of "for".

 

[Peter_SSs]

Here are the answers.

Thank you.

Question 1 wasn't really answered. I had seen the list in Sheet2. It has 147 items, but in Sheet1 column C you have just listed one on each row. I was asking how you got that one word on each row. I think that I have finally figured that out but I need to check because if I am right, you have quite a few errors with your expected results.

In the screen shot below I have highlighted some of the values that you placed in column C and used to get your results.

- All the amber ones are words that do not appear in column A of Sheet2 so why are they here in column C of Sheet1?
- The blue cell is on Sheet2 but so is "Save" which also appears in the text in A19, is further to the right than "to" and is still followed by a comma. So why are we not using "save" on that row?

Book1
	C
1	about
2	than
3	on
4	and
5	the
6	that
7	to
8	and
9	the
10	of
11	about
12	about
13	as
14	in
15	about
16	the
17	in
18	about
19	to
20	for
Sheet1

 

[satish78]

Thank you.

Question 1 wasn't really answered. I had seen the list in Sheet2. It has 147 items, but in Sheet1 column C you have just listed one on each row. I was asking how you got that one word on each row. I think that I have finally figured that out but I need to check because if I am right, you have quite a few errors with your expected results.

In the screen shot below I have highlighted some of the values that you placed in column C and used to get your results.

- All the amber ones are words that do not appear in column A of Sheet2 so why are they here in column C of Sheet1?
- The blue cell is on Sheet2 but so is "Save" which also appears in the text in A19, is further to the right than "to" and is still followed by a comma. So why are we not using "save" on that row?

Book1
	C
1	about
2	than
3	on
4	and
5	the
6	that
7	to
8	and
9	the
10	of
11	about
12	about
13	as
14	in
15	about
16	the
17	in
18	about
19	to
20	for
Sheet1

- All the amber ones are words that do not appear in column A of Sheet2 so why are they here in column C of Sheet1?
A: You are right. I made a mistake not including those words in Sheet2.

- The blue cell is on Sheet2 but so is "Save" which also appears in the text in A19, is further to the right than "to" and is still followed by a comma. So why are we not using "save" on that row?
A: You are right. That is also my mistake.

 

[sandy666]

so maybe post a valid example
and what if source text contain more the one keyword, eg. the, and, of, to ?
do you define keywords manually in column C ?

 

[sandy666]

example

Pre-screened and pre-counseled potential applicants; provided social and academic information about college life and experiences; guided campus tours; spoke on student panels; contacted, arranged

this text contain: and, and, about, and, on
you defined on only

I'm trying to understand what you really want to achieve

 

[Peter_SSs]

So out of 20 sample rows, 8 are incorrect. Perhaps now you would understand why we have questions and need to know the logic behind the request & why just looking at the sample data is not sufficient.

example

Pre-screened and pre-counseled potential applicants; provided social and academic information about college life and experiences; guided campus tours; spoke on student panels; contacted, arranged

this text contain: and, and, about, and, on
you defined on only

I'm trying to understand what you really want to achieve

I believe (but it would be good to have exact confirmation) that the gist of it is that "on" is the last of the defined words that is still followed (any time later) by a comma.

 

[Peter_SSs]

I would also like confirmation for this example "I live close to the beach, town and the forest" given that both "to" and "close to" are in the defined list.
Is the answer ..
a) "I live close" because "To" is the most right of the two choices? or
b) "I live" because "Close to" starts further to the left then "To" but finishes just as far to the right?

If a) is the correct answer then there is no point having "Close to" in the list in the first place.

 

[satish78]

I would also like confirmation for this example "I live close to the beach, town and the forest" given that both "to" and "close to" are in the defined list.
Is the answer ..
a) "I live close" because "To" is the most right of the two choices? or
b) "I live" because "Close to" starts further to the left then "To" but finishes just as far to the right?

If a) is the correct answer then there is no point having "Close to" in the list in the first place.

a) is the correct answer.

 

[satish78]

example

Pre-screened and pre-counseled potential applicants; provided social and academic information about college life and experiences; guided campus tours; spoke on student panels; contacted, arranged

this text contain: and, and, about, and, on
you defined on only

I'm trying to understand what you really want to achieve

You are right Sandy. define on only.