如何使用Excel宏实现两列并排排序及新旧表格匹配排序？

Hey there, let's tackle this problem head-on. You've got two tables sitting side-by-side in one Excel sheet—your old Table1 (columns A1, B1, C1, etc.) and new Table2 (columns A2, B2, C2, etc.), both using their respective "A" columns as unique IDs. The goal is to align them so matching IDs land on the same row, making it dead easy to spot new records, deleted entries, and modified data. Here's a robust VBA macro to pull this off, plus step-by-step breakdowns:

First, Clarify Your Setup #

To make the macro reliable, let's lock in a clear structure (adjust to match your actual sheet):

• Old table starts at cell A1 (header row: A1=A1, B1=B1, C1=C1...)
• New table starts at, say, F1 (header row: F1=A2, G1=B2, H1=C2...) — tweak this based on where your new table lives
• We'll add a helper column (default: column K) to flag record status: Match, New in Table2, or Deleted from Table1

The VBA Macro #

Open the VBA editor (press Alt + F11), insert a new module, and paste this code. I've added comments to walk you through every part:

Sub AlignOldNewTables()
    Dim ws As Worksheet
    Dim oldIDCol As Range, newIDCol As Range
    Dim oldLastRow As Long, newLastRow As Long
    Dim cell As Range, matchCell As Range
    Dim helperCol As Integer
    
    ' Set your target worksheet (replace "Sheet1" with your actual sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Define ID columns (adjust these to match your tables)
    Set oldIDCol = ws.Range("A:A") ' ID column for old Table1
    Set newIDCol = ws.Range("F:F") ' ID column for new Table2
    helperCol = 11 ' Helper column (K by default; change if needed)
    
    ' Clear old helper column data/formatting
    ws.Columns(helperCol).ClearContents
    ws.Columns(helperCol).ClearFormats
    
    ' Find the last row with data in both ID columns
    oldLastRow = oldIDCol.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    newLastRow = newIDCol.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    ' Mark matches and copy new Table2 records to the old table area
    For Each cell In newIDCol.Range(newIDCol.Cells(2), newIDCol.Cells(newLastRow))
        If cell.Value <> "" Then
            Set matchCell = oldIDCol.Find(cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
            If Not matchCell Is Nothing Then
                ' ID exists in both tables: flag as a match
                ws.Cells(matchCell.Row, helperCol).Value = "Match"
            Else
                ' ID is new to Table2: copy the entire row to the end of the old table
                oldLastRow = oldLastRow + 1
                ws.Rows(newIDCol.Cells(cell.Row, 1).Row).Copy ws.Rows(oldLastRow)
                ws.Cells(oldLastRow, helperCol).Value = "New in Table2"
            End If
        End If
    Next cell
    
    ' Mark records deleted from Table1 (IDs present in old table but not new)
    For Each cell In oldIDCol.Range(oldIDCol.Cells(2), oldIDCol.Cells(oldLastRow))
        If cell.Value <> "" And ws.Cells(cell.Row, helperCol).Value = "" Then
            ws.Cells(cell.Row, helperCol).Value = "Deleted from Table1"
        End If
    Next cell
    
    ' Sort the entire combined range by the old table's ID column
    ws.Range("A1", ws.Cells(oldLastRow, helperCol)).Sort _
        Key1:=ws.Range("A1"), Order1:=xlAscending, _
        Header:=xlYes, Orientation:=xlTopToBottom
    
    ' Optional: Format helper column for quick visual scanning
    With ws.Columns(helperCol)
        .AutoFit
        .FormatConditions.Add Type:=xlTextString, String:="New in Table2", TextOperator:=xlContains
        .FormatConditions(.FormatConditions.Count).Interior.Color = vbGreen
        .FormatConditions.Add Type:=xlTextString, String:="Deleted from Table1", TextOperator:=xlContains
        .FormatConditions(.FormatConditions.Count).Interior.Color = vbRed
        .FormatConditions.Add Type:=xlTextString, String:="Match", TextOperator:=xlContains
        .FormatConditions(.FormatConditions.Count).Interior.Color = vbYellow
    End With
    
    MsgBox "Alignment complete! Check column " & Split(ws.Cells(1, helperCol).Address, "$")(1) & " for status flags.", vbInformation
End Sub

Customize Before Running #

Tweak these lines to match your sheet's layout:

• Set ws = ThisWorkbook.Worksheets("Sheet1"): Replace "Sheet1" with your actual sheet name
• Set oldIDCol = ws.Range("A:A"): Update to the ID column of your old Table1
• Set newIDCol = ws.Range("F:F"): Update to the ID column of your new Table2
• helperCol = 11: Change this if you don't want to use column K as the helper

How It Works #

1. Prep: Clears any old helper column data to start fresh
2. Scan New Table: Loops through every ID in Table2—marks matches with Table1, and copies new records to the end of Table1
3. Scan Old Table: Flags IDs that exist in Table1 but not Table2 as deleted
4. Sort: Aligns all records by the old table's ID column so matches sit on the same row
5. Format: Highlights statuses with colors for quick visual checks

Quick Notes #

• Ensure both tables have a header row (row 1)
• The macro assumes IDs are unique in both tables
• Always back up your data before running macros—better safe than sorry!

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