1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Find strings listed in column into files and mark Yes or No in respective file name column

Discussion in 'VBA Macros' started by Samadhan Gaikwad, Sep 27, 2017.

  1. Samadhan Gaikwad

    Samadhan Gaikwad Member

    Messages:
    136
    I have strings listed in "A" column and file names are listed in column headers. I want to find whether string in "A" column exists in a file or not and add result in a file name column. I am hopeful that it is possible using vba.

    I tried using formula "vlookup" (when added data into sheet) but it is time consuming calculation and as each file has 22K lines and for just 100 files all rows counts to 22 lakh lines.
    Any help will be appreciated.

    Attached Files:

    Last edited: Sep 27, 2017
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,917
    I'd recommend uploading at least 1 sample target file (the file where you are performing search on).

    Without knowing structure of data being searched. It's bit hard to optimize it.
    Is the search being performed on flat text, tab delimited, comma separated or some other format? etc etc.
    Samadhan Gaikwad likes this.
  3. Samadhan Gaikwad

    Samadhan Gaikwad Member

    Messages:
    136
    Please find uploaded file.
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,917
    Assuming all files are in single folder and exact file names are used as header...

    Code (vb):
    Sub Demo_StringSearch_txt()
    Dim fPath As String: fPath = "C:\test\search\"
    Dim strContent As String
    Dim intFF As Integer: intFF = FreeFile()
    Dim myArr
    Dim i As Long, j As Long
    myArr = Range("A1", Cells(Cells(Rows.count, 1).End(xlUp).Row, Cells(1, Columns.count).End(xlToLeft).Column)).Value

    For i = 2 To UBound(myArr, 2)
        Open fPath & myArr(1, i) For Input As #intFF
        strContent = Input(LOF(intFF), intFF)
        Close #intFF
        For j = 2 To UBound(myArr)
            If InStr(strContent, myArr(j, 1)) > 0 Then
                myArr(j, i) = "Yes"
            End If
        Next
    Next

    Range("A1").Resize(UBound(myArr), UBound(myArr, 2)) = myArr


    End Sub
    See attached zip. Demo_Search_txtFile.xlsb has the code and output sheet ("Demo"). And 3 text files to search on is included.

    Change fPath to folder of your choosing.

    Attached Files:

    Last edited: Sep 27, 2017
    Samadhan Gaikwad likes this.
  5. Samadhan Gaikwad

    Samadhan Gaikwad Member

    Messages:
    136
    :awesome:Thank you so much, it's working like anything.
    Small but powerful code, saved my days of work.
  6. Samadhan Gaikwad

    Samadhan Gaikwad Member

    Messages:
    136
    I would also like to see file name which is currently in progress, so modified code. But that shows first file and then after macro is complete, last file. I do not see continuous progress. For an average, macro takes 1-2 minutes for each file and I need to process 100 to 500 files in one go.
    Code (vb):

    Sub Demo_StringSearch_txt()
    Application.ScreenUpdating = False
    Dim fPath As String: fPath = "D:\test\"
    Dim strContent As String
    Dim intFF As Integer: intFF = FreeFile()
    Dim myArr
    Dim i As Long, j As Long
    myArr = Range("A1", Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column)).Value
    'Application.StatusBar = file.Name
    For i = 2 To UBound(myArr, 2)
    Application.StatusBar = myArr(1, i)
        Open fPath & myArr(1, i) For Input As #intFF
        strContent = Input(LOF(intFF), intFF)
        Close #intFF
        For j = 2 To UBound(myArr)
            If InStr(strContent, myArr(j, 1)) > 0 Then
                myArr(j, i) = "Yes"
            End If
        Next
    Application.StatusBar = myArr(1, i)
    Next

    Range("A1").Resize(UBound(myArr), UBound(myArr, 2)) = myArr

    Application.ScreenUpdating = True
    End Sub

     
  7. Marc L

    Marc L Excel Ninja

    Messages:
    3,277
    Hi !

    Try DoEvents just before Next codeline …

    Just needs one StatusBar codeline, second is superfluous !
    Samadhan Gaikwad likes this.
  8. Samadhan Gaikwad

    Samadhan Gaikwad Member

    Messages:
    136
    Will you please modify above code, I didn't get you.
    Last edited by a moderator: Sep 29, 2017
  9. Marc L

    Marc L Excel Ninja

    Messages:
    3,277


    Just replace second StatusBar entire codeline by DoEvents statement !​
    Samadhan Gaikwad likes this.
  10. Samadhan Gaikwad

    Samadhan Gaikwad Member

    Messages:
    136
    Got it, working fine. I had forgotten that I had placed 2 same lines. Thank you so much!
  11. Samadhan Gaikwad

    Samadhan Gaikwad Member

    Messages:
    136
    Having strings in "A" column (around 40 K) and around 500 file names in columns, vba takes approx. 1.5 to 2 min. for each file (Overall 16 hours for the run).
    I have also made screen updating false during run. So can there be any way to increase performance of this macro?
  12. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,917
    Don't use VBA? :p

    Really, using right tool for the job may ease your pain. Look into investing time and/or money into proper DB.

    Or use GREP (GnuWIN, AstroGrep etc etc). Which can be used to spit out list of files that contain match to an expression.

    Other than that... try using RegEx pattern matching instead of Instr and see if it's any faster. I'm bit busy right now to write the code.
    Samadhan Gaikwad likes this.
  13. Samadhan Gaikwad

    Samadhan Gaikwad Member

    Messages:
    136
    Ok. No problem. My final goal is to compare 2 consecutive files if they contains same string. Yeah, I will keep finding other better solution.:)

Share This Page