1、打开任意Excel工作簿,右键单击任意sheet,点击查看代码打开Excel宏编辑器.
2、复制粘贴如下代码至编辑器:Sub 去重合并()arr = Worksheets("sheet1").Range("D2:Z65536").Cleararr = Worksheets("sheet1").UsedRangeSet d = CreateObject("Scripting.Dictionary")ReDim brr(1 To UBound(arr), 2)For n = 2 To UBound(arr)s = arr(n, 1)If d(s) = 0 Thenj = j + 1d(s) = jbrr(j, 1) = arr(n, 1)End IfIf brr(d(s), 2) = "" Thenbrr(d(s), 2) = arr(n, 2)Elsebrr(d(s), 2) = brr(d(s), 2) & "\" & arr(n, 2)End IfNextWorksheets("sheet1").[D2].Resize(UBound(brr), 1) = Application.Index(brr, 0, 2)Worksheets("sheet1").[E2].Resize(UBound(brr), 1) = Application.Index(brr, 0, 3)End Sub
3、切换回工作表界面依次点击开发工具---插入---按钮(窗体控件) 插入一个宏按钮
4、右键单击宏按钮选择指定宏... 选择去重合并宏并点击确定.
5、在工作表界面单击插入的宏按钮即可看到D列为A列去重后的数据,E列为B列合并后以"\"隔开的数据.
