单元格内数据去重

破而后立是为我

灵感来源于解决一位网友的问题。gif说明一切，不再赘述。
Sub sp()
On Error Resume Next
Dim str
For j = 2 To Range("a900").End(xlUp).Row
str = VBA.Split(Range("a" & j), ",")
str1 = str(0)
k = UBound(str)
    For i = 1 To k
        If VBA.InStr(str1, str(i)) > 0 Then
        str1 = str1
        Else
        str1 = str1 & "," & str(i)
        End If
    Next
    Range("b" & j) = str1
Next
End Sub

数据可以自行模仿，添加，尝试。

gufengaoyue

有bug, 你试试这几个数
1234,123,12,123
2222,2223,22,222

1. Sub sp改()
   
2. On Error Resume Next
   
3. Dim str
   
4. For j = 2 To Range("a900").End(xlUp).Row
   
5. str = VBA.Split(Range("a" & j), ",")
   
6. str1 = "," & str(0)
   
7. k = UBound(str)
   
8.     For i = 1 To k
   
9.         If VBA.InStr(str1 & ",", "," & str(i) & ",") > 0 Then
   
10.             str1 = str1
    
11.         Else
    
12.             str1 = str1 & "," & str(i)
    
13.         End If
    
14.     Next
    
15.     Range("b" & j) = Mid(str1, 2)
    
16. Next
    
17. End Sub

复制代码

02761752696

学习学习   谢谢分享

DevilW

确实不能处理111,11,1这种长、短字串,把字串两头封死就行了

Sub test()
    r = [a65536].End(3).Row
    ar = Range("a2:a" & r)
    For i = 1 To UBound(ar)
        br = Split(ar(i, 1), ",")
        x = ","
        For j = 0 To UBound(br)
            If InStr(x, "," & br(j) & ",") = 0 Then x = x & br(j) & ","
        Next
        ar(i, 1) = VBA.Mid(x, 2, Len(x) - 2)
    Next
    [b2].Resize(UBound(ar), 1) = ar
End Sub

破而后立是为我

gufengaoyue 发表于 2018-12-13 16:00
有bug, 你试试这几个数
1234,123,12,123
2222,2223,22,222

你们的更严谨一点儿，我这个原来是处理重复手机号的

LittleBird2006

习惯了用字典解决重复的问题。