Excel/VBA多工作表去重：月度SAP上传差异订阅数据提取需求

Hey Nick, let's walk through how to solve your two core tasks: identifying monthly transit subscription changes for SAP uploads, and removing duplicate rows across multiple Excel worksheets using VBA. I’ve broken this into practical, actionable steps:

Part 1: Extract Changed/New Records for SAP Upload #

First, we need to compare your current month's export with last month's data to isolate subscriptions that are new or have modified fields. Here’s the most efficient way to do this:

• Prepare Your Data:

  • Place current month’s data in a worksheet named CurrentMonth and last month’s data in LastMonth. Ensure both have identical column names (7 columns total) and no extra leading/trailing spaces in values.
  • If your data has a unique identifier (like a Subscription ID), use that as the primary match key—it’ll make comparisons far more accurate than checking all 7 columns for every row.

• Use Power Query for Efficient Comparison:

  1. Go to the Data tab → Click Get Data → From Table/Range for both CurrentMonth and LastMonth sheets. This loads each dataset into the Power Query Editor.
  2. In Power Query, go to Home → Merge Queries → Merge Queries as New. Choose CurrentMonth as the first table, LastMonth as the second, and select your unique identifier column (or all 7 columns if no ID exists) as the join key. Select Full Outer Join as the join kind.
  3. Expand the merged LastMonth columns, then add a custom column to flag differences:

     = if [LastMonth.SubscriptionID] = null then "New" else 
       List.AnyTrue(List.Transform(Record.FieldNames(LastMonth), each CurrentMonth[_] <> LastMonth[_])) then "Changed" else "Same"
     

  4. Filter the custom column to keep only New and Changed rows, then load the result to a new worksheet (e.g., SAP_Upload_Data). This is the dataset you’ll upload to SAP.

• Alternative: Formula-Based Approach (For Small Datasets):
  If you don’t want to use Power Query, use COUNTIFS or VLOOKUP to check for existing records. For example, in CurrentMonth, add a column with:

  =IF(ISNA(VLOOKUP(A2, LastMonth!$A:$G, 1, FALSE)), "New", IF(SUMPRODUCT(--(CurrentMonth!A2:G2=LastMonth!A$2:G$1000))=7, "Same", "Changed"))
  

  Then filter for "New" and "Changed" rows. Note this works best for smaller datasets (under 10k rows).

Part 2: VBA Code to Remove Duplicates Across Multiple Worksheets #

To eliminate duplicate rows across all your worksheets, use this VBA script—it collects all unique rows and outputs them to a new sheet:

Sub RemoveDuplicatesAcrossSheets()
    Dim ws As Worksheet
    Dim uniqueDict As Object
    Dim rowData As Variant
    Dim uniqueKey As String
    Dim outputSheet As Worksheet
    Dim nextOutputRow As Long
    
    ' Initialize dictionary to track unique rows
    Set uniqueDict = CreateObject("Scripting.Dictionary")
    uniqueDict.CompareMode = vbTextCompare ' Ignore case (adjust to vbBinaryCompare if case-sensitive)
    
    ' Create or reuse the output worksheet
    On Error Resume Next
    Set outputSheet = ThisWorkbook.Worksheets("Unique_Combined_Data")
    If Err.Number <> 0 Then
        Set outputSheet = ThisWorkbook.Worksheets.Add
        outputSheet.Name = "Unique_Combined_Data"
    End If
    On Error GoTo 0
    
    nextOutputRow = 2 ' Start below header row
    
    ' Loop through all worksheets
    For Each ws In ThisWorkbook.Worksheets
        ' Skip the output sheet to avoid reprocessing
        If ws.Name <> outputSheet.Name Then
            ' Loop through data rows (assuming header is in row 1)
            For Each rowData In ws.Range("A2:" & ws.Cells(ws.Rows.Count, "G").End(xlUp).Address).Rows
                ' Combine all 7 columns into a single key for duplication check
                uniqueKey = Join(Application.Transpose(Application.Transpose(rowData.Value)), "|")
                
                ' Add to output if key doesn't exist in dictionary
                If Not uniqueDict.Exists(uniqueKey) Then
                    uniqueDict.Add uniqueKey, True
                    rowData.Copy outputSheet.Cells(nextOutputRow, 1)
                    nextOutputRow = nextOutputRow + 1
                End If
            Next rowData
        End If
    Next ws
    
    ' Copy header from first worksheet to output sheet
    ThisWorkbook.Worksheets(1).Range("A1:G1").Copy outputSheet.Range("A1")
    
    MsgBox "Duplicates removed! Unique data is in the 'Unique_Combined_Data' sheet.", vbInformation
End Sub

• How to Use This Script:
  1. Open your Excel file → Press Alt + F11 to open the VBA Editor.
  2. Right-click your workbook in the Project Explorer → Insert → Module.
  3. Paste the code above into the module.
  4. Press F5 to run the macro, or assign it to a button in Excel for easy access.

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