Function 提取重覆(rg1 As Range, rg2 As Range)
Dim arr1, arr2
arr1 = Split(rg1, ",") '將長串數據1,按逗號拆分成一組數據
arr2 = Split(rg2, ",") '將長串數據2,按逗號拆分成一組數據
a = ""
For h = 0 To UBound(arr1) '逐個比對兩組數字
For i = 0 To UBound(arr2)
If arr1(h) = arr2(i) Then a = a & arr2(i) & "," '將兩組數據相同的部分留下,以","分隔相同數據,組成一連串字串
End If
Next
Next
Dim arr, d As Object '剔除结果中的重覆值
Set d = CreateObject("scripting.dictionary") '將d設為一個字典
arr = Split(Mid(a, 1, Len(a) - 1), ",") '將長串數據a,按逗號拆分成一組數據,順便將a字串最後一個逗號消除。
For i = 0 To UBound(arr)
d(arr(i)) = "" '不知道為什麼要將d(arr(i))設為空白
Next i
提取重复 = Join(d.keys, ",") '為什麼會用d.keys,看不懂
End Function