Results 1 to 9 of 9

Thread: VBA: Find matches in other workbooks for a list of values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post VBA: Find matches in other workbooks for a list of values

    Hi
    I have very little experience in VBA but need to puttogether some code to save me a great deal of time. To set the scene:
    Each day I get a list of style numbers for products. I have to check each style number against acollection of other workbooks and copy the corresponding row(s) to a mastersheet for the day.
    The ‘database’ workbooks are all in one folder and there arecurrently 39 of them. Each workbook hasmultiple worksheets.The workbooks aresent from the product suppliers each season so I would need to be able to updatethe list of ‘database’ workbooks with new files and updated filenames.
    I would like to the code to search for each style number inall of the ‘database’ workbooks and return the whole row for each match to anew workbook.
    I have found bits of code which will do some of this so Ithink what I am asking about is possible but have no idea how to put this alltogether into something that works.
    Any help much appreciated.
    Thank you


  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,879
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA: Find matches in other workbooks for a list of values

    Q1 Is the match simply matching the style number

    Q2 Is column A the lookup column in each worksheet in every workbook ?

    Q3
    Is there only ONE value to be returned for each style (ie the FIRST match) ?
    - cancelling the search after the first match each time would speed up the code enormously
    - if there are potentially several matches to be returned for each style number then please explain what you want

    Q4
    Are there any other files in the same folder as the 39 workbooks ?

    Q5 How big are your database workbooks ?
    - what I am thinking is that it would be better to amalgamate all the workbooks into one MEGA workbook for this purpose ( can be achieved by VBA )
    (one MEGA workbook would avoid opening 39 workbooks every day - much much faster)
    - obviously means that the "MEGA" workbook would need updating automatically by VBA whenever any of the 39 workbooks is updated

    To evaluate this suggestion, I need to know (approx) how many ROWS (with values in them) there are in total (ie all the rows with entries in all sheets in all 39 workbooks)
    Either your best guess at that TOTAL would be useful
    OR
    ...
    - what is the maximum number of sheets in any workbook ? (approx is good enough)
    - what is the maximum number of rows found in any sheet in any of the workbooks (approx is good enough)
    - what is a typical "number of sheets " in a workbook
    Last edited by Yongle; Sep 5th, 2019 at 11:35 AM.

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Find matches in other workbooks for a list of values

    Yongle thank you for responding.
    Here are my answers to your question:
    A1 Yes the match is simply the style number

    A2 The lookup needs to be for the whole worksheet. These come from external suppliers so the formatting is inconsistent

    A3 Unfortunately there could be multiple matches for a style. Once a match is found, all other matches would be in the same workbook. I need the full row copied for each match to the style number

    A4 There are no other files in the same folder

    A5 Each workbook is around 1Mb. If combining via VBA is the best option, the update of the MEGA workbook can be included as part of the searching code as I would not need a 'live' version of the mega workbook after the search is complete
    Maximum sheets per workbook should be 8
    Maximum rows per sheet would be 1500
    Typical sheets per workbook is 4

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,879
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA: Find matches in other workbooks for a list of values

    1. Expanding a bit further on the MEGA workbook ...
    - placing all the data in ONE sheet in MEGA would speed up the matching hugely and that looks possible (your A5 suggests that there are fewer than 250,000 rows in total)

    2. How often are the 39 underlying workbooks amended ?
    - how frequently do you expect each of the 39 workbooks to be updated by suppliers ?
    (just trying to work through how to automate MEGA's update)

    The lookup needs to be for the whole worksheet. These come from external suppliers so the formatting is inconsistent
    3. poses an interesting challenge to make the matching fast


    I will formulate draft code and post it either later today or tomorrow after I have considered everything fully
    Last edited by Yongle; Sep 6th, 2019 at 06:49 AM.

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Re: VBA: Find matches in other workbooks for a list of values

    Yongle

    You are wonderfully helpful.

    1. I would agree that there should be fewer than 250,000 total rows.

    2. I am not responsible for the supplier updates, these are handled by the buyers. To be certain, can the updates to the mega workbook run each time the search function runs.

    3. Speed is not that essential. I have access to a workstation I can run this code on without interruption. The key for me is to automate a task which consumes a lot of my time (mainly because of these multiple and inconsistent supplier workbooks). Even if the VBA version takes as long as the manual version, at least I can be doing something else whilst it runs!

  6. #6
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,879
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA: Find matches in other workbooks for a list of values

    I was hoping that we would not need to update MEGA every day
    - updating the MEGA sheet EVERY time would involve opening and closing 39 workbooks and transferring data from over 120 sheets and eventual matching in ONE sheet
    - doing the matching workbook by workbook would involve opening and closing 39 workbooks and matching in over 120 sheeets
    - I am not convinced that much time would be saved!
    So let's "do the obvious" first and then consider possible alternatives after we get that working satisfactorily

  7. #7
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,879
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA: Find matches in other workbooks for a list of values

    For the first test, do exactly as below, to help me sort any issues

    1 create a NEW workbook
    2 paste list of values to be matched into column A in Sheet1
    3 paste code below into a standard module
    4 amend aPath to the correct one , the string must end with the path separator
    5 run the code

    Columns AA:AC in each result row will include value being matched , workbook name, sheet name to help help you trace things when testing

    Code:
    Sub ridlesthwate()
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
    'variables
        Const aPath = "C:\???\???\"             ' ***   \  is last character in string!!
        Const Ext = "*.xl*"
        Dim wB As Workbook, wS As Worksheet, Results As Worksheet, aFile As String
        Dim Cel As Range, Rng As Range
        Dim findStr As String, rw As Range, r As Long
    'create results sheet and and specify range of values to match
        With ThisWorkbook.Sheets("Sheet1")
            Set Rng = .Range("A1", .Range("a" & .Rows.Count).End(xlUp))
        End With
        Set Results = ThisWorkbook.Sheets.Add
        
        aFile = Dir(aPath & Ext)
        Do While aFile <> ""
            Set wB = Workbooks.Open(aPath & aFile)
            DoEvents
            'find matches and copy rows to results sheet
            For Each wS In wB.Worksheets
                For Each rw In wS.UsedRange.Rows
                    For Each Cel In Rng
                        findStr = Cel.Value
                        If WorksheetFunction.CountIf(rw, findStr) > 0 Then
                            r = r + 1
                            rw.Copy Results.Cells(r, 1)
                            Results.Cells(r, "AA").Resize(, 3) = Array(findStr, wB.Name, wS.Name)    'delete line after testing is completed
                        End If
                    Next Cel
                Next rw
            Next
        
            wB.Close SaveChanges:=False
            DoEvents
            aFile = Dir
      Loop
      Application.Calculation = xlCalculationAutomatic
    End Sub
    Comment
    - the match is against the whole value in each cell (exactly the same as a basic VLookup match)
    - it is possible that it is not matching in the way you want
    - if that is the case then provide some examples of the values being matched and entire cell strings that are not matching up but should be

  8. #8
    New Member
    Join Date
    Sep 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Find matches in other workbooks for a list of values

    Yongle

    The code is running an matching values as I wanted it too.

    I have looked again at the supplier sheets, the values to be searched are always in columns F to J. Can this restriction be added to speed up the search?

    Whilst trying to understand how this code works, I came across the Application.Match function. Would you explain why this function is not used?

    Thank you again.

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,879
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA: Find matches in other workbooks for a list of values

    I have looked again at the supplier sheets, the values to be searched are always in columns F to J. Can this restriction be added to speed up the search?
    The match is tested in this line
    Code:
                If WorksheetFunction.CountIf(rw, findStr) > 0 Then
    Perhaps this replacement will be marginally faster
    Code:
                If WorksheetFunction.CountIf(rw.Resize(, 10), findStr) > 0 Then
    I came across the Application.Match function. Would you explain why this function is not used?
    "Rows in the used range" define the loop used (so the ROW address is known already)
    COUNTIF was the first function that occurred to me because testing the ROW for a match is sufficient (do not care where it is in the row)
    No particular reason that I did not use MATCH
    Range.FIND could also be used https://docs.microsoft.com/en-us/off...cel.range.find

    If you have time, you could test to see which one of the 3 is fastest etc

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •