说明:现在A列有许多单元格的值是相同的,现在需要将相同值对应的B列的单元格进行合并,如:
A列 B列
aa a1
aa a2
bb a3
bb a4
aa a1
aa a2
bb a3
bb a4
最后的结果为(a1/a2在一个单元格,a3/a4在一个单元格):
A列 B列
aa a1
a2
bb a3
a4
aa a1
a2
bb a3
a4
Sub 合并单元格()
Set x = CreateObject("scriptcontrol")
x.Language = "jscript"
x.eval "arr=new Array();function aa(aa,bb) {arr[aa]=arr[aa]+''+bb ;}; function cc() {kk=typeof arr + ',';for (i in arr) {kk +=i+','};return kk;}"
For i = 2 To [a2].End(4).Row
Call x.Run("aa", Cells(i, 1).Value, Cells(i, 2).Value)
Next
Set y = x.eval("arr")
Z = x.Run("cc")
arr = Split(Z, ",")
j = 1
For i = 1 To UBound(arr)
Cells(j, 3) = arr(i)
Cells(j, 4) = Replace(CallByName(y, arr(i), 2), "undefined,", "")
Cells(j, 5) = Replace(Cells(j, 4), ",", Chr(10)) '将逗号替换为换行符
j = j + 1
Next
End Sub
Set x = CreateObject("scriptcontrol")
x.Language = "jscript"
x.eval "arr=new Array();function aa(aa,bb) {arr[aa]=arr[aa]+''+bb ;}; function cc() {kk=typeof arr + ',';for (i in arr) {kk +=i+','};return kk;}"
For i = 2 To [a2].End(4).Row
Call x.Run("aa", Cells(i, 1).Value, Cells(i, 2).Value)
Next
Set y = x.eval("arr")
Z = x.Run("cc")
arr = Split(Z, ",")
j = 1
For i = 1 To UBound(arr)
Cells(j, 3) = arr(i)
Cells(j, 4) = Replace(CallByName(y, arr(i), 2), "undefined,", "")
Cells(j, 5) = Replace(Cells(j, 4), ",", Chr(10)) '将逗号替换为换行符
j = j + 1
Next
End Sub