如何用VBA按Project-ID合并两个年份数量不同的Excel数据集？

Got it, let's work through this step by step to get your merged data and pivot table set up exactly how you need it. Here's a practical VBA-based solution that covers both merging the datasets and building the custom pivot table you described.

Since Dataset 1 has unique Project-IDs with names, we'll use a dictionary to efficiently map these names to every matching entry in Dataset 2 (way faster than VLOOKUP for large datasets).

Sub MergeProjectData()
    Dim wsData1 As Worksheet, wsData2 As Worksheet, wsCombined As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long, i As Long
    Dim projNameMap As Object
    
    ' Update these sheet names to match your actual workbook
    Set wsData1 = ThisWorkbook.Sheets("Dataset1") ' Contains Proj.-ID + Project Name
    Set wsData2 = ThisWorkbook.Sheets("Dataset2") ' Contains Proj.-ID + Year + Index 1-3
    Set wsCombined = ThisWorkbook.Sheets.Add(After:=wsData2)
    wsCombined.Name = "MergedProjectData"
    
    ' Copy Dataset2's headers and data to the merged sheet
    wsData2.Range("A1").CurrentRegion.Copy wsCombined.Range("A1")
    ' Add a new column for Project Name
    wsCombined.Cells(1, wsData2.UsedRange.Columns.Count + 1).Value = "Project Name"
    
    ' Build a dictionary to map Project-IDs to their names
    Set projNameMap = CreateObject("Scripting.Dictionary")
    lastRow1 = wsData1.Cells(wsData1.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow1
        ' Convert ID to string to avoid format mismatches (e.g., number vs text)
        Dim projID As String
        projID = CStr(wsData1.Cells(i, "A").Value)
        
        If Not projNameMap.Exists(projID) Then
            projNameMap.Add projID, wsData1.Cells(i, "B").Value
        End If
    Next i
    
    ' Match names to every row in Dataset2
    lastRow2 = wsData2.Cells(wsData2.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow2
        projID = CStr(wsData2.Cells(i, "A").Value)
        
        If projNameMap.Exists(projID) Then
            wsCombined.Cells(i, wsCombined.Columns.Count).Value = projNameMap(projID)
        Else
            wsCombined.Cells(i, wsCombined.Columns.Count).Value = "No Matching Project"
        End If
    Next i
    
    ' Auto-fit columns for readability
    wsCombined.UsedRange.Columns.AutoFit
End Sub

Key Notes for Merging: #

• The dictionary ensures we only store each Project-ID once, making the matching process efficient even with large datasets.
• Converting IDs to strings prevents mismatches if one dataset stores IDs as numbers and the other as text.

Now we'll build a pivot table that shows each Project-ID only once, displays its name, and splits the index fields by year as columns.

Sub BuildProjectPivot()
    Dim wsCombined As Worksheet, wsPivot As Worksheet
    Dim pivotCache As PivotCache
    Dim pivotTable As PivotTable
    Dim sourceRange As Range
    
    Set wsCombined = ThisWorkbook.Sheets("MergedProjectData")
    ' Create a new sheet for the pivot table
    Set wsPivot = ThisWorkbook.Sheets.Add(After:=wsCombined)
    wsPivot.Name = "ProjectSummaryPivot"
    
    ' Define the pivot table data source
    Set sourceRange = wsCombined.UsedRange
    Set pivotCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=sourceRange)
    
    ' Create the pivot table
    Set pivotTable = pivotCache.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A1"), _
        TableName:="ProjectYearlySummary")
    
    ' Configure pivot table fields
    With pivotTable
        ' Set row fields: Proj.-ID first, then Project Name
        .PivotFields("Proj.-ID").Orientation = xlRowField
        .PivotFields("Project Name").Orientation = xlRowField
        
        ' Critical: Make Project Name only show once per Project-ID
        .PivotFields("Project Name").LayoutForm = xlOutline
        .PivotFields("Project Name").RepeatLabels = False
        
        ' Set Year as column field (splits data by year horizontally)
        .PivotFields("Year").Orientation = xlColumnField
        
        ' Add index fields as values (adjust function and names to match your needs)
        With .PivotFields("索引1")
            .Orientation = xlDataField
            .Function = xlSum ' Use xlCount if you need counts instead of sums
            .Name = "索引1 汇总"
        End With
        With .PivotFields("索引2")
            .Orientation = xlDataField
            .Function = xlSum
            .Name = "索引2 汇总"
        End With
        With .PivotFields("索引3")
            .Orientation = xlDataField
            .Function = xlSum
            .Name = "索引3 汇总"
        End With
        
        ' Optional: Apply a clean pivot table style
        .TableStyle2 = "PivotStyleMedium9"
    End With
End Sub

Key Notes for the Pivot Table: #

• Setting RepeatLabels = False for the Project Name field ensures it only appears once under each Project-ID, instead of repeating on every row.
• Using Year as a column field will arrange your index data horizontally by year, exactly as you requested.

• Test with a small sample of your data first to confirm matches and pivot table layout are correct.
• If you don't need to keep the merged dataset long-term, you can hide or delete the MergedProjectData sheet after creating the pivot table.
• Adjust the xlSum function to xlCount, xlAverage, etc., based on how you want to aggregate your index fields.

内容的提问来源于stack exchange，提问作者M.Groß