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

 

[sandy666]

what about ?

so maybe post a valid example

so this is a true?

do you define keywords manually in column C ?

 

[sandy666]

this is what you want? (green table)

if so, use Power Query (Get&Transform)

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ETBD = Table.TransformColumns(Source, {{"RAW", each Text.BeforeDelimiter(_, ",", {0, RelativePosition.FromEnd}), type text}}),
    ITBD = Table.AddColumn(ETBD, "Text Before Delimiter", each Text.BeforeDelimiter([RAW], " "&[keywords], {0, RelativePosition.FromEnd}), type text),
    ROC = Table.SelectColumns(ITBD,{"Text Before Delimiter"})
in
    ROC

 

[Peter_SSs]

a) is the correct answer.

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

So, actually, you have all the following entries in Sheet 2 that are not required as they are multiple words where the last word is already in the list. They won't really hurt being there but it does introduce more processing that needed.

Book1
	P
1	According to
2	Ahead of
3	Along with
4	Apart from
5	As per
6	As to
7	As well as
8	Aside from
9	Away from
10	Because of
11	By means of
12	Close to
13	Contrary to
14	Depending on
15	Due to
16	Forward of
17	Further to
18	In addition to
19	In between
20	In case of
21	In favor of
22	In front of
23	In lieu of
24	In spite of
25	In the face of
26	In view of
27	Instead of
28	Irrespective of
29	Near to
30	Next to
31	On account of
32	On behalf of
33	On to
34	On top of
35	Opposite to
36	Other than
37	Out of
38	Outside of
39	Owing to
40	Preparatory to
41	Prior to
42	Regardless of
43	Thanks to
44	Together with
45	Up against
46	Up to
47	Up until
48	With reference to
49	With regard to
Sheet1

 

[Peter_SSs]

a) is the correct answer.

In that case, try this user-defined function.

Function BeforeComma(s As String, rWords As Range) As String
  Static RX As Object
  Dim Bits As Variant
  Dim tmp As String
  Dim i As Long
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.IgnoreCase = True
  End If
  RX.Pattern = "^\b(" & Join(Application.Transpose(rWords), "|") & ")\b[^#]*\,"
  Bits = Split(s)
  For i = UBound(Bits) To 0 Step -1
    tmp = Bits(i) & " " & tmp
    If RX.test(tmp) Then
      BeforeComma = Trim(Left(s, Len(s) - Len(tmp)))
      Exit For
    End If
  Next i
End Function

Here is the sample data and results (using the original list in column A of Sheet2 of the sample file that you provided).

Book1
	A	B
1	Coordinated team in bringing about Business Requirements, Architectural Specifications, Corporate	Coordinated team in bringing
2	Major accomplishment: The data transfer from about 95 databases, are continuously loaded into one database in less than 15 minutes, after numerous aggregations and calculations.	Major accomplishment: The data transfer from about 95 databases, are continuously loaded into one database in less
3	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, and visited high schools to represent and promote Colorado State University.	Pre-screened and pre-counseled potential applicants; provided social and academic information about college life and experiences; guided campus tours; spoke
4	· Hobbies: Reading about grid computing, computer forensics and data analysis, Arts	· Hobbies: Reading
5	College Editor Revamped and elevated quality of alumnae magazine by giving marketing-focused publication a journalistic slant through the telling of vibrant stories about the students, alumnae, and faculty.	College Editor Revamped and elevated quality of alumnae magazine by giving marketing-focused publication a journalistic slant through the telling of vibrant stories
6	ï‚·Developed a smart rule engine for CAS that tracks, alerts, emails and generates Crystal/Jasper reports for decision-makers in a company about any customer-employee issue whenever the rule is applicable.
7	Administrator Module to create, edits, delete and enquire about the registered	Administrator Module
8	about 3,000 bank branches in some two dozen western and mid western states, in addition to	about 3,000 bank branches
9	information about the visitor's behavior, tracking campaign effectively and see how visitors found the site, how	information
10	and brand identity; marketing, cross-selling of products, as well as building customer awareness about the organization and its services.	and brand identity; marketing, cross-selling
11	Regular Interaction with the client team appraising them about project status, risks, challenges & resource	Regular Interaction with the client team appraising them
12	Regular Interaction with the client team appraising them about project status, risks, challenges & resource	Regular Interaction with the client team appraising them
13	Knowledge about different process such as Negative news check, transaction settlement ,	Knowledge about different process such
14	5% of revenue to about 30% in 4 years, improved service quality, shortened time to market for new offerings	5% of revenue to about 30%
15	Gained knowledge about networking protocols (IPv6, TCP), IP Networking Infrastructure and its	Gained knowledge
16	Gained knowledge about the North American digital hierarchy including DS0, DS1, DS3 and	Gained knowledge about the North American digital hierarchy
17	social network in planning, budgeting, shopping and communicating about her wedding	social network
18	for a beef plant with an annual revenue of about $600,000,000.	for a beef plant with an annual revenue of
19	of educating them about technologies to increase business productivity, save time, and meet	of educating them about technologies to increase business productivity,
20	traffic flow for the Bands, designed Trophies & Plaques, appeared and spoke about the show on the local TV
Sheet1

Cell Formulas
Range	Formula
B1:B20	B1	=BeforeComma(A1,Sheet2!A$1:A$147)

 

[sandy666]

@Peter_SSs
row 4 is not compatible with the user example

 

[Peter_SSs]

@Peter_SSs
row 4 is not compatible with the user example

In what way? In post 14 the OP admitted a mistake in that row (& several others)

 

[sandy666]

so maybe I overlooked that, sorry
that is why I asked about correct example

 

[satish78]

this is what you want? (green table)
View attachment 4376
if so, use Power Query (Get&Transform)

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ETBD = Table.TransformColumns(Source, {{"RAW", each Text.BeforeDelimiter(_, ",", {0, RelativePosition.FromEnd}), type text}}),
    ITBD = Table.AddColumn(ETBD, "Text Before Delimiter", each Text.BeforeDelimiter([RAW], " "&[keywords], {0, RelativePosition.FromEnd}), type text),
    ROC = Table.SelectColumns(ITBD,{"Text Before Delimiter"})
in
    ROC

Sandy, your power query looks good and does extractly what I need.

I do not know how to use power query.
Can you convert the code into vba?

 

[satish78]

In that case, try this user-defined function.

Function BeforeComma(s As String, rWords As Range) As String
  Static RX As Object
  Dim Bits As Variant
  Dim tmp As String
  Dim i As Long
 
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.IgnoreCase = True
  End If
  RX.Pattern = "^\b(" & Join(Application.Transpose(rWords), "|") & ")\b[^#]*\,"
  Bits = Split(s)
  For i = UBound(Bits) To 0 Step -1
    tmp = Bits(i) & " " & tmp
    If RX.test(tmp) Then
      BeforeComma = Trim(Left(s, Len(s) - Len(tmp)))
      Exit For
    End If
  Next i
End Function

Here is the sample data and results (using the original list in column A of Sheet2 of the sample file that you provided).

Book1
	A	B
1	Coordinated team in bringing about Business Requirements, Architectural Specifications, Corporate	Coordinated team in bringing
2	Major accomplishment: The data transfer from about 95 databases, are continuously loaded into one database in less than 15 minutes, after numerous aggregations and calculations.	Major accomplishment: The data transfer from about 95 databases, are continuously loaded into one database in less
3	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, and visited high schools to represent and promote Colorado State University.	Pre-screened and pre-counseled potential applicants; provided social and academic information about college life and experiences; guided campus tours; spoke
4	· Hobbies: Reading about grid computing, computer forensics and data analysis, Arts	· Hobbies: Reading
5	College Editor Revamped and elevated quality of alumnae magazine by giving marketing-focused publication a journalistic slant through the telling of vibrant stories about the students, alumnae, and faculty.	College Editor Revamped and elevated quality of alumnae magazine by giving marketing-focused publication a journalistic slant through the telling of vibrant stories
6	ï‚·Developed a smart rule engine for CAS that tracks, alerts, emails and generates Crystal/Jasper reports for decision-makers in a company about any customer-employee issue whenever the rule is applicable.
7	Administrator Module to create, edits, delete and enquire about the registered	Administrator Module
8	about 3,000 bank branches in some two dozen western and mid western states, in addition to	about 3,000 bank branches
9	information about the visitor's behavior, tracking campaign effectively and see how visitors found the site, how	information
10	and brand identity; marketing, cross-selling of products, as well as building customer awareness about the organization and its services.	and brand identity; marketing, cross-selling
11	Regular Interaction with the client team appraising them about project status, risks, challenges & resource	Regular Interaction with the client team appraising them
12	Regular Interaction with the client team appraising them about project status, risks, challenges & resource	Regular Interaction with the client team appraising them
13	Knowledge about different process such as Negative news check, transaction settlement ,	Knowledge about different process such
14	5% of revenue to about 30% in 4 years, improved service quality, shortened time to market for new offerings	5% of revenue to about 30%
15	Gained knowledge about networking protocols (IPv6, TCP), IP Networking Infrastructure and its	Gained knowledge
16	Gained knowledge about the North American digital hierarchy including DS0, DS1, DS3 and	Gained knowledge about the North American digital hierarchy
17	social network in planning, budgeting, shopping and communicating about her wedding	social network
18	for a beef plant with an annual revenue of about $600,000,000.	for a beef plant with an annual revenue of
19	of educating them about technologies to increase business productivity, save time, and meet	of educating them about technologies to increase business productivity,
20	traffic flow for the Bands, designed Trophies & Plaques, appeared and spoke about the show on the local TV
Sheet1

Cell Formulas
Range	Formula
B1:B20	B1	=BeforeComma(A1,Sheet2!A$1:A$147)

Your VBA does not do correct extraction.
It have to extract like Sandy's Power query.

 

[sandy666]

I do not know how to use power query.

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

Can you convert the code into vba?

I am against vba so I can't help you

maybe someone else