samwang

¦^´_ 1# stephenlee


¤£¦n·N«ä¡A¤£¤Ó¯à²z¸ÑXÄ檺µ²ªG¦p¦ó²£¥X¡A¥i§_½Ð§A¦A¸Ô²Ó»¡©ú³W«h¡A·PÁ¡C

samwang

¦^´_ 3# stephenlee

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C

Sub test()
Dim Arr, xD, i%, i2&, N%
Arr = Range([F1], [Q65536].End(3))
Set xD = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(Arr)
    If Not xD.Exists(Arr(i, 12) & "") Then
        xD(Arr(i, 12) & "") = ""
        For i2 = i To UBound(Arr)
            If xD.Exists(Arr(i2, 12) & "") Then N = N + 1
        Next
        Cells(i, 24) = Arr(i, 1) & "~" & N: N = 0
    End If
Next
End Sub

samwang

¦^´_ 6# stephenlee

¤£¦n·N«ä¨Sª`·N¨ì¡A½Ð¦A´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C

Sub test2()
Dim Arr, xD, i%, i2&, N%
Arr = Range([F1], [Q65536].End(3))
Set xD = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(Arr)
    If Not xD.Exists(Arr(i, 12) & "") Then
        xD(Arr(i, 12) & "") = i
        For i2 = i To UBound(Arr)
            N = xD(Arr(i2, 12) & ""): If N = 0 Then Exit For
        Next
        Cells(i, 24) = Arr(i, 1) & "~" & Arr(i2 - 1, 1)
    End If
Next
End Sub

samwang

¦^´_ 8# stephenlee

½Ð¦A´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C

Sub test3()
Dim Arr, xD, i%, i2&, N%
Arr = Range([F1], [Q65536].End(3))
Set xD = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(Arr)
    If Not xD.Exists(Arr(i, 12) & "") Then
        xD(Arr(i, 12) & "") = i
        For i2 = i To UBound(Arr)
           If Not xD.Exists(Arr(i2, 12) & "") Then Exit For
        Next
        Cells(i, 24) = Arr(i, 1) & "~" & Arr(i2 - 1, 1)
    End If
Next
End Sub

samwang

¦^´_ 10# stephenlee

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C

Sub test()
Dim Arr, a, a1
Arr = Range("A2:A" & [A65536].End(3).Row)
For i = 1 To UBound(Arr)
    a = Split(Arr(i, 1), "-")(0)
    a1 = Split(Arr(i, 1), "-")(1)
    If a = a1 Then Arr(i, 1) = a
Next
Range("B2").Resize(UBound(Arr)).NumberFormatLocal = "@"
Range("B2").Resize(UBound(Arr)) = Arr
End Sub