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
- 输出到新表
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
- 输出到新表
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突出显示重复条目,以便轻松删除它们。看这里