Excel 2011（Mac）合并重复行问题：Scripting.Dictionary无法使用

Hey there, I feel your pain—Excel 2011 for Mac has some quirky limitations when it comes to VBA, and the missing Scripting.Dictionary is a big one for tasks like merging duplicate attendance entries. Let's break down two solid workarounds that should get you sorted.

Since Excel 2011 for Mac doesn't support the Scripting.Dictionary object (that's exactly what's blocking your original approach), we can use Excel's built-in Collection object to replicate the duplicate-merging logic. Here's a tailored script for your attendance report:

Sub MergeDuplicateAttendanceEntries()
    Dim wsSource As Worksheet
    Dim wsOutput As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim entryKey As String
    Dim attendanceColl As New Collection
    Dim item As Variant
    Dim outputRow As Long
    
    ' Set your source worksheet (update "Attendance" to match your sheet name)
    Set wsSource = ThisWorkbook.Worksheets("Attendance")
    ' Create a new sheet to store merged results
    Set wsOutput = ThisWorkbook.Worksheets.Add
    wsOutput.Name = "MergedAttendance"
    
    ' Copy header row to the output sheet
    wsSource.Rows(1).Copy Destination:=wsOutput.Rows(1)
    outputRow = 2
    
    ' Get the last row of data in your source sheet
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    On Error Resume Next ' Ignore duplicate key errors in the Collection
    For i = 2 To lastRow
        ' Create a unique key to identify duplicates (adjust columns as needed)
        ' Example: Combine Student ID (Col A) + Date (Col B) as the unique key
        entryKey = wsSource.Cells(i, "A").Value & "|" & wsSource.Cells(i, "B").Value
        
        ' Add entry to collection: key = unique ID, item = concatenated attendance data
        ' Update columns (C, D) to match your actual attendance fields (status, check-in time, etc.)
        attendanceColl.Add _
            Key:=entryKey, _
            Item:=wsSource.Cells(i, "A").Value & "|" & _
                  wsSource.Cells(i, "B").Value & "|" & _
                  wsSource.Cells(i, "C").Value & ";" & _
                  wsSource.Cells(i, "D").Value
                  
        ' If key already exists, append new attendance data to the existing entry
        If Err.Number = 457 Then
            Err.Clear
            Dim existingData As String
            existingData = attendanceColl(entryKey)
            ' Append new data to the attendance details section
            attendanceColl(entryKey) = Left(existingData, InStrRev(existingData, "|") + 1) & _
                                      Mid(existingData, InStrRev(existingData, "|") + 1) & ";" & _
                                      wsSource.Cells(i, "D").Value
        End If
    Next i
    On Error GoTo 0 ' Reset error handling
    
    ' Write merged data from the collection to the output sheet
    For Each item In attendanceColl
        Dim dataParts As Variant
        dataParts = Split(item, "|")
        
        wsOutput.Cells(outputRow, "A").Value = dataParts(0) ' Student ID
        wsOutput.Cells(outputRow, "B").Value = dataParts(1) ' Date
        wsOutput.Cells(outputRow, "C").Value = Split(dataParts(2), ";")(0) ' Keep original attendance type
        wsOutput.Cells(outputRow, "D").Value = dataParts(2) ' Merged attendance details
        
        outputRow = outputRow + 1
    Next item
    
    ' Auto-fit columns for readability
    wsOutput.Columns.AutoFit
    
    MsgBox "Duplicate entries merged successfully!", vbInformation
End Sub

Quick Customization Tips: #

• Update sheet names ("Attendance", "MergedAttendance") to match your workbook.
• Adjust the entryKey logic (columns A and B) to match what defines a duplicate in your report (e.g., Student ID + Course Name instead of Date).
• Modify the columns used in the Item concatenation to fit your specific attendance data fields.

If you'd rather avoid VBA entirely, use Excel's built-in tools to merge duplicates manually:

1. Create a unique key helper column:

   • Add a new column (e.g., Column E) with this formula to generate a unique identifier for each entry:

     =A2&"|"&B2
     

   • Copy the formula down to all rows of data.
   • Select Column E, go to Data > Remove Duplicates to extract a list of unique keys (copy these to a new sheet as your base).

2. Merge attendance data for unique entries:

   • Use an array formula to concatenate matching data (press Cmd+Shift+Enter instead of just Enter to activate it):

     =TEXTJOIN(";", TRUE, IF($E$2:$E$100=F2, $D$2:$D$100, ""))
     

     Replace E2:E100 with your helper column range, F2 with the unique key in your new sheet, and D2:D100 with your attendance data column.
   • If TEXTJOIN isn't available in Excel 2011, use this alternative array formula:

     =CONCATENATE(IF($E$2:$E$100=F2, $D$2:$D$100&";", ""))
     

3. Clean up and format:

   • Use VLOOKUP to pull in additional student details (name, ID, etc.) for each unique key.
   • Optionally, use a PivotTable to summarize data: drag the unique key field to Rows, and your attendance data to Values (set to "Count" or "Max" as needed).

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