技术需求：使用VBA替代跨工作表多条件Index Match公式实现数据匹配与Qty ordered求和

Got it, I totally get the pain of slow Index/Match formulas on large datasets—they can crawl when you've got tons of cells running them. Let's build a VBA solution that'll handle your 3-criteria match and sum task way faster, since it processes data in bulk instead of per-cell calculations.

Your Matching & Sum Requirements #

• Match Customer Name between the "Monitor" sheet and the "Master" sheet's Customer column
• Match Expected Delivery Date (top-left area in "Monitor") with the Expected delivery date column in "Master"
• Match Mesh (table header in "Monitor") with the Mesh column in "Master"
• Sum the corresponding Qty ordered values when all three criteria are met, then populate the result in the correct position in "Monitor"

VBA Code Implementation #

Press Alt + F11 to open the VBA Editor, insert a new module (Insert > Module), and paste this code:

Sub SumMatch3Criteria()
    Dim wsMaster As Worksheet, wsMonitor As Worksheet
    Dim masterData As Variant, monitorData As Variant
    Dim masterRow As Long, monitorRow As Long, monitorCol As Long
    Dim deliveryDate As Date, customer As String, mesh As String
    Dim totalQty As Double
    
    ' Set worksheet references (adjust names if your sheets have different labels)
    Set wsMaster = ThisWorkbook.Worksheets("Master")
    Set wsMonitor = ThisWorkbook.Worksheets("Monitor")
    
    ' Load all data into arrays (this is the big performance boost—no cell-by-cell reads!)
    masterData = wsMaster.UsedRange.Value
    monitorData = wsMonitor.UsedRange.Value
    
    ' Clear existing results in Monitor to avoid leftover old data
    wsMonitor.UsedRange.Offset(1, 1).ClearContents ' Adjust offset if your header starts in a different spot
    
    ' Grab the expected delivery date from Monitor's top-left area (adjust cell reference if needed)
    deliveryDate = wsMonitor.Range("A1").Value
    
    ' Loop through each customer row in Monitor (skip header row, start at row 2)
    For monitorRow = 2 To UBound(monitorData, 1)
        customer = monitorData(monitorRow, 1) ' Assumes Customer is in column A of Monitor
        
        ' Loop through each Mesh column in Monitor (skip first column, start at column 2)
        For monitorCol = 2 To UBound(monitorData, 2)
            mesh = monitorData(1, monitorCol) ' Mesh labels are in Monitor's header row
            totalQty = 0
            
            ' Scan Master data to match all criteria and sum quantities
            For masterRow = 2 To UBound(masterData, 1) ' Skip Master's header row
                ' Check all three match conditions (adjust column indices to match your Master sheet!)
                If masterData(masterRow, 1) = customer _ ' Customer column in Master (A)
                    And masterData(masterRow, 2) = deliveryDate _ ' Expected Delivery Date column (B)
                    And masterData(masterRow, 3) = mesh _ ' Mesh column (C)
                    Then
                    totalQty = totalQty + masterData(masterRow, 4) ' Qty ordered column (D)
                End If
            Next masterRow
            
            ' Populate the summed quantity into the correct Monitor cell
            wsMonitor.Cells(monitorRow, monitorCol).Value = totalQty
        Next monitorCol
    Next monitorRow
    
    MsgBox "Matching and summing completed successfully!", vbInformation
End Sub

Key Adjustments for Your Sheets #

You’ll need to tweak the column indices in the code to match your actual sheet layout:

• In masterData, adjust the numbers (1, 2, 3, 4) to line up with where Customer, Expected delivery date, Mesh, and Qty ordered live in your "Master" sheet.
• In monitorData, confirm Customer is in column A (rows 2+) and Mesh headers are in row 1 (columns 2+). Also, change the deliveryDate cell reference (A1) if your top-left date is in a different spot.

How to Run #

1. Save your workbook as a .xlsm file (macros won’t work in regular .xlsx files).
2. Press Alt + F8, select SumMatch3Criteria, and click "Run".

This should handle your large dataset way more efficiently than individual Index/Match formulas!

内容的提问来源于stack exchange，提问作者cap36