Excel - 将多个列组合到一个列中

时间:2022-07-20 04:24:53

My excel sheet looks like this

我的excel表看起来像这样

      c1        c2      c3      c4

ROW1   abc      def      1       2

ROW2   abc      def      3       4

ROW3   klm      efg     11       5

ROW4   klm      efg     12      89

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条目。所以excel表应该是这样的,

       c1        c2      c3      c4

ROW1   abc      def      1,3     2,4

ROW2   klm      efg     11,12    5,89

2 个解决方案

#1


5  

This code will

这段代码会

  • Run on columns A:D on the current sheet
  • 在当前工作表上的A:D列上运行
  • Group records that are common in columns A and B togther with combined column C and column D values respectively
  • A列和B列中常见的组记录分别与组合的C列和D列组合在一起
  • runs case insensitive
  • 运行不区分大小写
  • outputs to a new sheet
  • 输出到新表

Excel  - 将多个列组合到一个列中

    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

#2


0  

You can do this using the excel concatenate function. Here's a link to a good tutorial

您可以使用excel concatenate函数执行此操作。这是一个很好的教程的链接

Also, to deal with the duplicates, you can have excel highlight duplicate entries so they can be easily deleted. See here

此外,要处理重复项,您可以使用Excel突出显示重复条目,以便轻松删除它们。看这里

#1


5  

This code will

这段代码会

  • Run on columns A:D on the current sheet
  • 在当前工作表上的A:D列上运行
  • Group records that are common in columns A and B togther with combined column C and column D values respectively
  • A列和B列中常见的组记录分别与组合的C列和D列组合在一起
  • runs case insensitive
  • 运行不区分大小写
  • outputs to a new sheet
  • 输出到新表

Excel  - 将多个列组合到一个列中

    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

#2


0  

You can do this using the excel concatenate function. Here's a link to a good tutorial

您可以使用excel concatenate函数执行此操作。这是一个很好的教程的链接

Also, to deal with the duplicates, you can have excel highlight duplicate entries so they can be easily deleted. See here

此外,要处理重复项,您可以使用Excel突出显示重复条目,以便轻松删除它们。看这里