EXCEL判断第一列如果相同，则合并第二列中的值到一个单元格，中间用逗号隔开_hanfen

个人资料

微博

正文字体大小：大中小

EXCEL判断第一列如果相同，则合并第二列中的值到一个单元格，中间用逗号隔开

(2022-10-24 16:12:03)

分类：工作相关

假定原数据在A、B两列

=IF(COUNTIF(A$1:A1,A1)>1,"",A1) 下拉填充

=IF(COUNTIF(A$1:A1,A1)>1,"",SUBSTITUTE(SUBSTITUTE(PHONETIC(OFFSET(A1,,,COUNTIF(A:A,A1),2)),A1,","),",","",1)) 下拉填充

————————————————

原文链接：https://blog.csdn.net/weixiaobeihout/article/details/123849573

有时候报错可用如下代码

Sub test()

Application.ScreenUpdating = False

n = 1

Set d = CreateObject("scripting.dictionary")

arr = [a1].CurrentRegion

For i = 1 To UBound(arr)

d(arr(i, 1)) = d(arr(i, 1)) & arr(i, 2) & "、"

[e1].Resize(d.Count) = Application.Transpose(d.keys)

cnt = d.Count

k = d.items

d.RemoveAll

For j = 0 To cnt

On Error Resume Next

brr = Split(k(j), ",")

For m = 0 To UBound(brr)

d(brr(m)) = ""

Cells(n, "f") = Join(d.keys, ",")

Cells(n, "F") = Left(Cells(n, "f"), Len(Cells(n, "f")) - 1)

n = n + 1

d.RemoveAll

Cells(Rows.Count, "F").End(3).Delete

Application.ScreenUpdating = False

End Sub

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report