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

 

[Rick Rothstein]

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.

For the text in cell A2 (Sheet 1 from your example file)... why did you use the word "than" for the match when the word "about" occurs earlier in the text?

 

[satish78]

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.

For the text in cell A2 (Sheet 1 from your example file)... why did you use the word "than" for the match when the word "about" occurs earlier in the text?

Because the vba have to find from right to left.

 

[Peter_SSs]

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

Because the vba

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"?

 

[juddaaaa]

Give this a try. Let me know if that's what you're trying to do.

Sub FindStrings()

    Dim rngS1 As Range, rngS2 As Range
    Dim strS1 As String, strS2 As String, strWords As String
    Dim intPosition As Integer

    With Sheets("Sheet1")
        For Each rngS1 In .Range("A1:A" & GetLastRow(Sheets("Sheet1"), "A"))
            intPosition = Len(rngS1.Value)
            strS1 = Left(rngS1.Value, Len(rngS1.Value) - (Len(rngS1.Value) - InStr(1, rngS1.Value, ",") + 1))
       
            For Each rngS2 In Sheets("Sheet2").Range("A1:A" & GetLastRow(Sheets("Sheet2"), "A"))
                strS2 = " " & rngS2.Value & " "
           
                If InStr(1, strS1, strS2, 1) < intPosition Then
                    intPosition = IIf(InStr(1, strS1, strS2, 1) > 0, InStr(1, strS1, strS2, 1), intPosition)
                    strWords = IIf(InStr(1, strS1, strS2, 1) > 0, strS2, strWords)
                End If
            Next rngS2
           
            .Range("C" & rngS1.Row).Value = IIf(InStr(1, strS1, strWords, 1), strWords, Empty)
            .Range("D" & rngS1.Row).Value = IIf(.Range("C" & rngS1.Row).Value <> Empty, Left(strS1, Len(strS1) - (Len(strS1) - InStr(1, strS1, .Range("C" & rngS1.Row).Value, 1) + 1)), Empty)
        Next rngS1
    End With

End Sub

Function GetLastRow(Sh As Worksheet, refCol As String) As Long

    GetLastRow = Sh.Cells(Sh.Rows.Count, refCol).End(xlUp).Row

End Function

Book1
	A	B	C	D
1	Coordinated team in bringing about Business Requirements, Architectural Specifications, Corporate	need forumula	In	Coordinated team
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.	need forumula	From	Major accomplishment: The data transfer
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.	need forumula	About	Pre-screened and pre-counseled potential applicants; provided social and academic information
4	· Hobbies: Reading about grid computing, computer forensics and data analysis, Arts	need forumula	About	· 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.	need forumula	Of	College Editor Revamped and elevated quality
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.	need forumula
7	Administrator Module to create, edits, delete and enquire about the registered	need forumula	To	Administrator Module
8	about 3,000 bank branches in some two dozen western and mid western states, in addition to	need forumula	About
9	information about the visitor's behavior, tracking campaign effectively and see how visitors found the site, how	need forumula	About	information
10	and brand identity; marketing, cross-selling of products, as well as building customer awareness about the organization and its services.	need forumula
11	Regular Interaction with the client team appraising them about project status, risks, challenges & resource	need forumula	With	Regular Interaction
12	Regular Interaction with the client team appraising them about project status, risks, challenges & resource	need forumula	With	Regular Interaction
13	Knowledge about different process such as Negative news check, transaction settlement ,	need forumula	About	Knowledge
14	5% of revenue to about 30% in 4 years, improved service quality, shortened time to market for new offerings	need forumula	Of	5%
15	Gained knowledge about networking protocols (IPv6, TCP), IP Networking Infrastructure and its	need forumula	About	Gained knowledge
16	Gained knowledge about the North American digital hierarchy including DS0, DS1, DS3 and	need forumula	About	Gained knowledge
17	social network in planning, budgeting, shopping and communicating about her wedding	need forumula	In	social network
18	for a beef plant with an annual revenue of about $600,000,000.	need forumula	With	for a beef plant
19	of educating them about technologies to increase business productivity, save time, and meet	need forumula	Of
20	traffic flow for the Bands, designed Trophies & Plaques, appeared and spoke about the show on the local TV	need forumula
Sheet1

 

[satish78]

Above vba does not output what I am looking.
I said that we are going to pull data before a comma in every cell in ColumnA.

For example, in ColumnA A1

Coordinated team in bringing about Business Requirements, Architectural Specifications, Corporate

the word "about" is before the comma(with preceding "business requirements"), then why the VBA is pulling word "in" instead of "about"

 

[Peter_SSs]

I said that we are going to pull data before a comma in every cell in ColumnA.

In your example in post 6 there are multiple commas and you have extracted data before the first comma.
In other rows you have extracted data past the first comma. How do we know which comma to use? Just trying to understand the logic of what is required.

.. and any chance of answers to my earlier questions?

 

[satish78]

In your example in post 6 there are multiple commas and you have extracted data before the first comma.
In other rows you have extracted data past the first comma. How do we know which comma to use? Just trying to understand the logic of what is required.

.. and any chance of answers to my earlier questions?

I am trying to find words from backwards searching. Hope you understand now.

 

[Peter_SSs]

Hope you understand now.

No, sorry, you haven't answered any of my questions. I asked them because they were things that I did not understand.

 

[satish78]

No, sorry, you haven't answered any of my questions. I asked them because they were things that I did not understand.

If you are talking about juddaaaa post xl2bb.xlam table. That is not I want to get the output.