|
Option Explicit
Sub TEST6()
Dim ar, br, i&, j&, k&, dic() As New Dictionary, vKey
Application.ScreenUpdating = False
ReDim dic(2)
With Worksheets(1)
ar = Range(.[A1], .UsedRange).Value
For i = 1 To UBound(ar)
dic(0).RemoveAll: dic(1).RemoveAll
For j = 1 To UBound(ar, 2)
If Len(ar(i, j)) Then
br = Split(ar(i, j), ".")
For k = 0 To UBound(br)
dic(0)(br(k)) = dic(0)(br(k)) + 1
If dic(0)(br(k)) > 1 Then dic(1)(br(k)) = Empty
Next k
End If
Next j
If dic(1).Count Then
For j = 1 To UBound(ar, 2)
If Len(ar(i, j)) Then
br = Split(ar(i, j), ".")
dic(2).RemoveAll
For k = 0 To UBound(br)
dic(2)(br(k)) = Empty
Next k
For Each vKey In dic(2)
If dic(1).Exists(vKey) Then dic(2).Remove vKey
Next
ar(i, j) = Join(dic(2).Keys, ".")
End If
Next j
End If
Next i
.[A1].Resize(UBound(ar), UBound(ar, 2)) = ar
End With
Erase dic
Application.ScreenUpdating = True
Beep
End Sub
|
|