Excel - 将多个列组合到单个列 [英] Excel - combine multiple columns to a single column

问题描述

我的excel表格如下

My excel sheet looks like this

      c1        c2      c3      c4

ROW1   abc      def      1       2

ROW2   abc      def      3       4

ROW3   klm      efg     11       5

ROW4   klm      efg     12      89

我想将数据合并成一个单独的列，用一个逗号分隔，用于重复的c1条目。所以excel表应该这样，

I want to combine the data into one single column separated by one comma for duplicate c1 entries. So excel sheet should look like this,

       c1        c2      c3      c4

ROW1   abc      def      1,3     2,4

ROW2   klm      efg     11,12    5,89

推荐答案

此代码将

• 在当前工作表上的列A：D上运行


• 在列A和B中常见的组记录分别与组合的列C和列D值分别


• 运行不区分大小写


• 输出到新工作表

• Run on columns A:D on the current sheet
• Group records that are common in columns A and B togther with combined column C and column D values respectively
• runs case insensitive
• outputs to a new sheet

    Sub QuickCombine()
    Dim X()
    Dim Y()
    Dim objDic As Object
    Dim lngRow As Long
    Dim lngCol As Long
    Dim ws As Worksheet

    X = Range([a1], Cells(Rows.Count, "D").End(xlUp))
    Y = X
    Set objDic = CreateObject("scripting.dictionary")

    For lngRow = 1 To UBound(X, 1)
        If Not objDic.exists(LCase$(X(lngRow, 1) & X(lngRow, 2))) Then
            objDic.Add LCase$(X(lngRow, 1) & X(lngRow, 2)), lngRow
        Else
            Y(lngRow, 1) = vbNullString
            Y(objDic.Item(LCase$(X(lngRow, 1) & X(lngRow, 2))), 3) = Y(objDic.Item(LCase$(X(lngRow, 1) & X(lngRow, 2))), 3) & "," & X(lngRow, 3)
            Y(objDic.Item(LCase$(X(lngRow, 1) & X(lngRow, 2))), 4) = Y(objDic.Item(LCase$(X(lngRow, 1) & X(lngRow, 2))), 4) & "," & X(lngRow, 4)
        End If
    Next

    Set ws = Sheets.Add

    ws.[a1].Resize(UBound(X, 1), UBound(X, 2)) = Y
    ws.Columns("A").SpecialCells(xlBlanks).EntireRow.Delete

End Sub

这篇关于Excel - 将多个列组合到单个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！