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

satish78

Board Regular
Joined
Aug 31, 2014
Messages
180
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

 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,563
Office Version
2010
Platform
Windows
Rich (BB code):
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

Board Regular
Joined
Aug 31, 2014
Messages
180
Rich (BB code):
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,584
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jan 4, 2020
Messages
200
Office Version
365
Platform
Windows
Give this a try. Let me know if that's what you're trying to do.

VBA Code:
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
ABCD
1 Coordinated team in bringing about Business Requirements, Architectural Specifications, Corporateneed forumula In Coordinated team
2Major 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
3Pre-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, Artsneed forumula About · Hobbies: Reading
5College 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 registeredneed forumula To Administrator Module
8 about 3,000 bank branches in some two dozen western and mid western states, in addition toneed forumula About
9 information about the visitor's behavior, tracking campaign effectively and see how visitors found the site, howneed 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 & resourceneed forumula With Regular Interaction
12 Regular Interaction with the client team appraising them about project status, risks, challenges & resourceneed forumula With Regular Interaction
13Knowledge 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 offeringsneed forumula Of 5%
15 Gained knowledge about networking protocols (IPv6, TCP), IP Networking Infrastructure and itsneed forumula About Gained knowledge
16 Gained knowledge about the North American digital hierarchy including DS0, DS1, DS3 andneed forumula About Gained knowledge
17 social network in planning, budgeting, shopping and communicating about her weddingneed 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 meetneed forumula Of
20 traffic flow for the Bands, designed Trophies & Plaques, appeared and spoke about the show on the local TVneed forumula
Sheet1
 

satish78

Board Regular
Joined
Aug 31, 2014
Messages
180
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,584
Office Version
365
Platform
Windows
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

Board Regular
Joined
Aug 31, 2014
Messages
180
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.
 

Forum statistics

Threads
1,085,149
Messages
5,381,992
Members
401,764
Latest member
pzippel

Some videos you may like

This Week's Hot Topics

Top