求助：如何用宏实现转置复制粘贴？批量处理相同Seq no.的Unit ID

Hey Ramon, I feel your pain—manually transposing thousands of Unit IDs by Seq no. sounds like a total time sink. Let’s break down three solid methods to automate this in Excel, depending on your version and workflow preferences.

This is the quickest formula-based solution if you're on a modern Excel version:

• Assume your data starts at row 2 (row 1 is headers). In cell D2, paste this formula:
  =TEXTJOIN(", ", TRUE, IF($C$2:$C$1000=C2, $B$2:$B$1000, ""))
  • Replace $C$2:$C$1000 and $B$2:$B$1000 with your actual data range (extend the 1000 to match your last row)
  • For Excel 365/2021, just hit Enter. For older Excel versions, press Ctrl+Shift+Enter to run it as an array formula
• Drag the formula down the entire D column. It’ll automatically pull all matching Unit IDs for each Seq no., separated by commas (swap ", " with another delimiter like "|" or " " if you prefer)

Power Query is perfect for bulk data manipulation without formulas:

1. Select your entire data range (including headers), go to the Data tab → click From Table/Range (check "My table has headers" if prompted)
2. In the Power Query Editor, select column C (Seq no.), go to the Transform tab → click Group By
3. In the Group By window:
   • Group by: Seq no.
   • New column name: Unit IDs
   • Operation: All Rows
4. Click OK, then click the expand icon next to the Unit IDs column header → choose Extract Values
5. Pick your preferred delimiter (e.g., comma) and click OK
6. Click Close & Load to export the cleaned data to a new worksheet. You can then match this back to your original table or use it directly

If you need to run this repeatedly, a VBA macro will save you tons of time:

1. Press Alt+F11 to open the VBA Editor
2. Right-click your workbook in the Project Explorer → Insert → Module
3. Paste this code into the module:

Sub TransposeUnitIDsBySeq()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim seqDict As Object
    Dim i As Long
    Dim currentSeq As String
    Dim unitIDs As String
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    Set seqDict = CreateObject("Scripting.Dictionary")
    
    ' Store all Unit IDs per Seq no. in a dictionary
    For i = 2 To lastRow
        currentSeq = ws.Cells(i, "C").Value
        If seqDict.Exists(currentSeq) Then
            seqDict(currentSeq) = seqDict(currentSeq) & ", " & ws.Cells(i, "B").Value
        Else
            seqDict(currentSeq) = ws.Cells(i, "B").Value
        End If
    Next i
    
    ' Write the combined Unit IDs back to column D
    For i = 2 To lastRow
        currentSeq = ws.Cells(i, "C").Value
        ws.Cells(i, "D").Value = seqDict(currentSeq)
    Next i
    
    MsgBox "Processing complete!", vbInformation
End Sub

4. Press F5 to run the macro, or go back to Excel, open the Developer tab → Macros → select TransposeUnitIDsBySeq and click Run

• Pro tip: Always back up your data before running VBA macros, just in case!

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