Excel宏转置问题：将两列BOM清单按条件转换为多列

原代码问题分析 #

你提供的代码无法实现需求，核心问题在于：

• assignedCells的范围计算逻辑完全错误：WorksheetFunction.CountA(valueCell.EntireColumn) -1是统计A列所有非空单元格数量，以此调整B列的单元格范围，完全不符合“收集当前产品所有组件”的逻辑。
• 没有实现产品去重和组件聚合的核心逻辑，只是逐行复制原表内容，所以新表和原表完全一致。

修正后的VBA代码 #

Sub ConvertBOMToMultiColumn()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim lastRow As Long, i As Long, targetRow As Long, colIndex As Long
    Dim productDict As Object
    Dim productKey As Variant
    Dim componentArr As Variant
    Dim componentVal As String
    
    ' 设置源工作表（可手动指定，比如ThisWorkbook.Worksheets("原BOM表")）
    Set wsSource = ThisWorkbook.ActiveSheet
    ' 添加新工作表作为目标表
    Set wsTarget = ThisWorkbook.Worksheets.Add
    wsTarget.Name = "多列BOM结果"
    
    ' 初始化字典，用于存储产品和对应的组件列表
    Set productDict = CreateObject("Scripting.Dictionary")
    
    ' 获取源表A列最后一行
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    ' 遍历源表，收集产品和组件
    For i = 1 To lastRow
        productKey = Trim(wsSource.Cells(i, "A").Value)
        componentVal = Trim(wsSource.Cells(i, "B").Value)
        
        ' 跳过空的产品或组件
        If productKey <> "" And componentVal <> "" Then
            If productDict.Exists(productKey) Then
                ' 产品已存在，追加组件到列表
                productDict(productKey) = productDict(productKey) & "," & componentVal
            Else
                ' 产品不存在，新建条目
                productDict(productKey) = componentVal
            End If
        End If
    Next i
    
    ' 将字典内容写入目标表
    targetRow = 1
    For Each productKey In productDict.Keys
        ' 写入产品到第一列
        wsTarget.Cells(targetRow, "A").Value = productKey
        ' 拆分组件字符串为数组
        componentArr = Split(productDict(productKey), ",")
        ' 写入组件到后续列
        For colIndex = 0 To UBound(componentArr)
            wsTarget.Cells(targetRow, colIndex + 2).Value = componentArr(colIndex)
        Next colIndex
        targetRow = targetRow + 1
    Next productKey
    
    MsgBox "BOM格式转换完成，结果已写入新工作表。"
End Sub

代码说明 #

1. 字典去重与聚合：利用Scripting.Dictionary的键唯一性自动实现产品去重，每个键对应的值存储该产品的所有组件（用逗号分隔的字符串）。
2. 遍历收集数据：逐行读取源表的产品和组件，将组件聚合到对应产品的条目下。
3. 写入目标表：遍历字典的每个产品，将产品写入第一列，拆分组件字符串为数组后依次写入后续列。

使用方法 #

1. 打开你的BOM工作表，按下Alt + F11打开VBA编辑器。
2. 右键点击当前工作簿，选择「插入」→「模块」。
3. 将上述代码粘贴到模块窗口中。
4. 回到Excel界面，按下Alt + F8，选择ConvertBOMToMultiColumn宏并执行。

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