请求编写VBA宏：按Study计算唯一测试步骤数之和并填充至Metrics calculator工作表

Got it, here's a tailored macro that groups each study (column D) and sums only the unique values from the "No of test steps" column (column G), then outputs results in your requested format:

Sub CalculateUniqueStepsPerStudy()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim studyDict As Object
    Dim stepsDict As Object
    Dim i As Long
    Dim currentStudy As String
    Dim currentStep As Long
    Dim outputRow As Long
    
    ' Target the specified worksheet
    Set ws = ThisWorkbook.Worksheets("Metrics calculator")
    ' Find the last row with data in column D
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
    
    ' Use dictionaries to track studies and their unique steps
    Set studyDict = CreateObject("Scripting.Dictionary")
    
    ' Loop through all data rows (assuming row 1 is headers)
    For i = 2 To lastRow
        currentStudy = ws.Cells(i, "D").Value
        currentStep = ws.Cells(i, "G").Value
        
        ' Initialize a sub-dictionary for new studies
        If Not studyDict.Exists(currentStudy) Then
            Set stepsDict = CreateObject("Scripting.Dictionary")
            studyDict.Add currentStudy, stepsDict
        End If
        
        ' Add only unique step values to the study's sub-dictionary
        Set stepsDict = studyDict(currentStudy)
        If Not stepsDict.Exists(currentStep) Then
            stepsDict.Add currentStep, True
        End If
    Next i
    
    ' Create a new sheet to output results (adjust if you want to use the same sheet)
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Study Step Summary"
    outputRow = 1
    
    ' Write header row
    With ws.Parent.Worksheets("Study Step Summary")
        .Cells(outputRow, 1).Value = "Study no"
        .Cells(outputRow, 2).Value = "Total no of steps"
    End With
    outputRow = outputRow + 1
    
    ' Calculate sums and write results
    For Each currentStudy In studyDict.Keys
        Set stepsDict = studyDict(currentStudy)
        Dim totalSteps As Long
        totalSteps = 0
        
        ' Sum all unique step values for the study
        For Each currentStep In stepsDict.Keys
            totalSteps = totalSteps + currentStep
        Next currentStep
        
        ' Write to output sheet
        With ws.Parent.Worksheets("Study Step Summary")
            .Cells(outputRow, 1).Value = currentStudy
            .Cells(outputRow, 2).Value = totalSteps
        End With
        outputRow = outputRow + 1
    Next currentStudy
    
    MsgBox "Calculation done! Check the 'Study Step Summary' sheet for results.", vbInformation
End Sub

Quick breakdown of how this works: #

• Dictionaries for uniqueness: We use two levels of dictionaries—one to track each study, and a nested dictionary to store only unique test step values for each study (duplicates get automatically skipped).
• Data processing: The loop runs through every row of your data, populating the dictionaries with studies and their unique steps.
• Result output: A new sheet is created to display results with your exact requested headers. If you prefer to write results in the same worksheet, just modify the output section to target a specific range (like starting at column I, row 1).

Quick notes: #

• Double-check your worksheet name matches Metrics calculator exactly (it’s case-sensitive in some Excel versions).
• If your data starts at a row other than 2 (e.g., row 3 if you have extra header rows), adjust the For i = 2 To lastRow line to match your starting row.

内容的提问来源于stack exchange，提问作者Hariprasad G Iyer