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

[ridlesthwate]

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

 

[Yongle]

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

 

[ridlesthwate]

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

 

[Yongle]

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

 

[ridlesthwate]

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!

 

[Yongle]

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

 

[Yongle]

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

Sub ridlesthwate()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
[COLOR=#006400][I]'variables[/I][/COLOR]
    Const aPath = "[B]C:\???\???[COLOR=#ff0000]\[/COLOR][/B]"             ' ***   [B][COLOR=#ff0000]\[/COLOR][/B]  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
[COLOR=#006400][I]'create results sheet and and specify range of values to match[/I][/COLOR]
    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
       [COLOR=#006400][I] 'find matches and copy rows to results sheet[/I][/COLOR]
        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)   [COLOR=#0000cd][B] 'delete line after testing is completed[/B][/COLOR]
                    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

 

[ridlesthwate]

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.

 

[Yongle]

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

            If WorksheetFunction.CountIf(rw, findStr) > 0 Then

Perhaps this replacement will be marginally faster

            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/office/vba/api/excel.range.find

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