• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to find string in another string

ThrottleWorks

Excel Ninja
Hi,

Happy new year !

I have two sets of strings.

One is key word, another is actual value.

I need to find key word in actual value. If I find key word in actual value, then I need to populate value adjourning to key word next to actual value.

For example, key word is “Yamaha”, adjourning value to key word is “Yamaha ABC 123 – A1B2”, actual value is “dddedefeq afg SGGAG 45vbad Yamaha fga bfg gqe vb”

Once I get “Yamaha” in actual value, I will populate “Yamaha ABC 123 – A1B2” before “dddedefeq afg SGGAG 45vbad Yamaha fga bfg gqe vb”

Can anyone please help me in this.
 

Attachments

  • KeyWord.xls
    24.5 KB · Views: 9
  • Output.xls
    24 KB · Views: 7
Please note, number of key words are unique, might be in hundreds. But the actual values, the sheet where I am finding key words can be duplicates and in thousands.

Also, there may be cased where key words would not be present in actual values. We will leave such cases blank.
 
Hi,

I am using below code to search results. However it is taking more than 5 minutes to complete.
Can anyone please suggest me faster way to do this.

FromFindRng = 2,000 + values
InFindRng = 20,000 + values

Code:
Dim FromFindRng As Range
    Dim InFindRng As Range
  
    Dim Frng As Range
    Dim Irng As Range
      
    TempLr = DealNameSht.Cells(DealNameSht.Rows.Count, 2).End(xlUp).Row
    Set FromFindRng = DealNameSht.Range(DealNameSht.Cells(2, 2), DealNameSht.Cells(TempLr, 2))
  
    TempLr = TempSht.Cells(TempSht.Rows.Count, 3).End(xlUp).Row
  
    TempSht.Range("G2:G" & TempLr).FormulaR1C1 = "=RC[-4]&RC[-3]&RC[-2]&RC[-1]"
    TempSht.Range("G2:G" & TempLr).Value = TempSht.Range("G2:G" & TempLr).Value
    Set InFindRng = TempSht.Range(TempSht.Cells(2, 7), TempSht.Cells(TempLr, 7))
  
    TempSht.Select
    Range("A1").Select
    For Each Irng In InFindRng
        For Each Frng In FromFindRng
            If Irng.Offset(0, -1).Value = "" Then
                If InStr(1, Irng, Frng, 1) Then
                    Irng.Offset(0, -5).Value = Frng
                    Irng.Offset(0, -6).Value = Frng.Offset(0, -1).Value
                End If
            End If
        Next Frng
    Next Irng
 
Hi,

I used below mentioned code. This code filter each value rather than finding.
However, this also takes more than 3 minutes to run. Can anyone please suggest me a better solution.

Code:
Dim FromFindRng As Range
    Dim InFindRng As Range
  
    Dim Frng As Range
    Dim Irng As Range
      
    TempLr = DealNameSht.Cells(DealNameSht.Rows.Count, 2).End(xlUp).Row
    Set FromFindRng = DealNameSht.Range(DealNameSht.Cells(2, 2), DealNameSht.Cells(TempLr, 2))
  
    TempLr = TempSht.Cells(TempSht.Rows.Count, 3).End(xlUp).Row
  
    TempSht.Range("G2:G" & TempLr).FormulaR1C1 = "=RC[-4]&RC[-3]&RC[-2]&RC[-1]"
    TempSht.Range("G2:G" & TempLr).Value = TempSht.Range("G2:G" & TempLr).Value
    Set InFindRng = TempSht.Range(TempSht.Cells(2, 7), TempSht.Cells(TempLr, 7))
  
    Range("G1").Value = "DDD"
    Range("A1").Select
  
    On Error Resume Next
        TempSht.ShowAllData
    On Error GoTo 0
  
    For Each Frng In FromFindRng
        On Error Resume Next
            TempSht.ShowAllData
        On Error GoTo 0
      
        TempLr = TempSht.Cells(TempSht.Rows.Count, 3).End(xlUp).Row
      
        Set TempRng = TempSht.Range(TempSht.Cells(1, 1), TempSht.Cells(TempLr, 7))
        TempRng.AutoFilter Field:=7, Criteria1:="=*" & Frng & "*", Operator:=xlAnd
  
        TempLr = TempSht.Cells(TempSht.Rows.Count, 3).End(xlUp).Row
        If TempLr <> 1 Then
            Set TempRng = TempSht.Range(TempSht.Cells(2, 2), TempSht.Cells(TempLr, 2))
            TempRng.SpecialCells(xlCellTypeVisible).Value = Frng
          
            Set TempRng = TempSht.Range(TempSht.Cells(2, 1), TempSht.Cells(TempLr, 1))
            TempRng.SpecialCells(xlCellTypeVisible).Value = Frng.Offset(0, -1).Value
        End If
    Next Frng
 
Hi Sachin ,

You are likely to get a faster response if you upload your code in the form of a workbook which also has data in it.

To understand your requirement only from your description of it , and to come up with code which improves on what you already have , means those who wish to help have to put in some more effort , effort which can be reduced if you can help.

Narayan
 
Good afternoon @NARAYANK991 sir, thanks for the help.
I have uploaded a sample file in the first reply however you are correct, my latest reply does not have sample file.

Could you please check attached file if you get time.

Have a nice day ahead. :)
 

Attachments

  • Book1.xlsx
    11.1 KB · Views: 5
Hi ,

Sorry , but the file does not clear anything.

1. What are the two tabs for ?

2. What is the meaning of the following :

These are unique words, will be approximately 2,000 +

Where will these 2000+ words be listed ?

What is the connection between the data in A2 ( Tata Motors limited ABC 123 ) , and the data in B2 , C2 and D2 ?

3. What is the data in the tab named Search Here ?

4. Why have you highlighted certain data items in the various columns ?

Probably if you upload a workbook which contains realistic data to which you want to apply a solution , it may make things clear.

Narayan
 
Hi @NARAYANK991 sir, sorry for confusion and late reply.

1. What are the two tabs for ?: 'Key word', in this worksheet, all the key words are placed. Macro will search key words from this worksheet in 'Search Here' worksheet.

2. Where will these 2000+ words be listed: these will be listed in Range B2:D2000 of 'Key word' worksheet. Please note, each value in this range will be unique. There will not be any duplicates in this range.

3. What is the connection between the data in A2: Range A2 value 'Tata Motors limited ABC 123' is the corresponding value for range B2:D2. For example if I am able to find 'Tata motors' from Key word worksheet in Range D3 of 'Search Here' worksheet. My key word will be 'Tata motors' and related value will be 'Tata Motors limited ABC 123'.

4. What is the data in the tab named Search Here: Column A and B will populated based on the findings. These are output. Column C:G is raw data. Key words from 'Key word' worksheet range B2:D2 are supposed to be searched in 'Search Here' worksheet.

There is also a preference in searching. For example, I will search range (key word) B2:D2 in Column (search here) in Column C first, if no results found then I will move to next column.

5. Why have you highlighted certain data items in the various columns:
To highlight, what was the key word I find and in which cell I found the key word.

6. if you upload a workbook which contains realistic data: My apology for not uploading more clear data. I re-create data in another machine while uploading. That is why sample size is poor.

Thanks a lot for the help. :)
 
Back
Top