Excel宏开发需求：将SAP导出数据多行合并为单行

下面是直接可用的VBA宏，能把分散在多行的ID属性合并为单行，自动将Features转为列：

Sub SAPDataConsolidate()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim lastRow As Long, targetRow As Long
    Dim currentID As String, i As Long, j As Long
    Dim featureCol As Collection, featureName As String, featureValue As String
    
    ' 设置源工作表和目标工作表
    Set wsSource = ActiveSheet
    Set wsTarget = ThisWorkbook.Sheets.Add(After:=wsSource)
    wsTarget.Name = "整理后数据"
    
    ' 写入目标表表头
    wsTarget.Cells(1, 1) = wsSource.Cells(1, 1).Value ' ID列
    targetRow = 2
    Set featureCol = New Collection
    
    ' 收集所有唯一的Features作为目标表列名
    lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        featureName = wsSource.Cells(i, 2).Value
        On Error Resume Next
        featureCol.Add featureName, Key:=UCase(featureName)
        On Error GoTo 0
    Next i
    
    ' 写入Features表头
    For j = 1 To featureCol.Count
        wsTarget.Cells(1, j + 1) = featureCol(j)
    Next j
    
    ' 遍历源表，合并同一ID的属性
    currentID = wsSource.Cells(2, 1).Value
    wsTarget.Cells(targetRow, 1) = currentID
    
    For i = 2 To lastRow
        If wsSource.Cells(i, 1).Value = currentID Then
            ' 找到对应Feature列，写入值
            featureName = wsSource.Cells(i, 2).Value
            featureValue = wsSource.Cells(i, 3).Value
            For j = 1 To featureCol.Count
                If featureCol(j) = featureName Then
                    wsTarget.Cells(targetRow, j + 1) = featureValue
                    Exit For
                End If
            Next j
        Else
            ' 处理新ID
            targetRow = targetRow + 1
            currentID = wsSource.Cells(i, 1).Value
            wsTarget.Cells(targetRow, 1) = currentID
            ' 写入当前行的Feature值
            featureName = wsSource.Cells(i, 2).Value
            featureValue = wsSource.Cells(i, 3).Value
            For j = 1 To featureCol.Count
                If featureCol(j) = featureName Then
                    wsTarget.Cells(targetRow, j + 1) = featureValue
                    Exit For
                End If
            Next j
        End If
    Next i
    
    ' 自动调整目标表列宽
    wsTarget.UsedRange.Columns.AutoFit
    MsgBox "数据整理完成！结果在工作表「整理后数据」中。", vbInformation
End Sub

使用说明 #

• 确保源数据格式：第一列是ID，第二列是Features，第三列是属性值，表头在第一行
• 打开源数据Excel文件，按Alt+F11打开VBA编辑器
• 插入新模块，粘贴上述代码
• 返回Excel，按Alt+F8选择SAPDataConsolidate宏执行

注意事项 #

• 宏会自动创建「整理后数据」新工作表，不会修改源数据
• 支持字母数字、Yes/No等任意类型属性值
• 若源数据存在重复的ID+Feature组合，宏将保留最后一行的属性值

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