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

satish78

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

 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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?
 
Upvote 0
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.
 
Upvote 0
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"?
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,775
Members
448,298
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top