|
本帖最后由 mdhsjtu 于 2014-7-23 12:27 编辑
工作急需,求那位大神帮帮忙。具体是这样的:数据一共有2列,20341行,要求合并A列中相同的单元格(相同的只要一个),并依据A列中相同的单元格,把B列对应的单元格内容合并,并且用逗号隔开,最好能把合并后B列中重复出现的内容(两逗号之间的)删除。。。
A 列 B列
数学 A,B
语文 B,E
数学 B,C
英语 D
语文 D,E
数学 C
...
合并后变成:
A列 B列
数学 A,B,C
语文 B,D,E
英语 D
...
本帖最后由 易安1 于 2014-7-23 12:51 编辑
- 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) & ","
- Next
- [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)) = ""
- Next
- Cells(n, "f") = Join(d.keys, ",")
- Cells(n, "F") = Left(Cells(n, "f"), Len(Cells(n, "f")) - 1)
- n = n + 1
- d.RemoveAll
- Next
- Cells(Rows.Count, "F").End(3).Delete
- Application.ScreenUpdating = False
- End Sub
复制代码
|
|