How to discover repeat text names in Range array?

[jiura]

Hello to all!
I use Excel 2003 and I have an array of text data. This array consist of 15-20 text items in one column. I want to know has this array repeated text names or not. If I use Visual Basic with cycle in witch take’s the first name and then compare it with another’s, then take’s the second e.t.c. it ‘s very long. How can I do it faster? If I only want to know has this array repeated text names or not.

[Siddharth Rout]

it ‘s very long. How can I do it faster?

If you sort the array and then do check, that will also be longer. The best way is to copy the array to excel cells and then sort them and then compare them. that is the fastest...

For Example (Please amend it as per you requirements...)

Sub aaa()
 
'Defining my array for test purpose
Dim ar(21)
For i = 1 To 20
    ar(i) = "aaa" & i
    'Creating a duplicate value
    ar(21) = "aaa1"
Next i
 
 
'Copy Data to Sheet
For j = 1 To 21
    Sheets("sheet1").Range("A" & j) = ar(j)
Next j
 
'Sort The data
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
 
'Do the Comparing
For k = 1 To 21
    If Range("A" & (k + 1)).Value = Range("A" & k).Value Then
        MsgBox "Match Found"
    End If
Next k
 
End Sub

[Colin_L]

#2 is already a good suggestion. Here's my 2 cents....

If your names are already in a range in the worksheet then you could just use the COUNTIF worksheet function + conditional formatting to highlight duplicate values.

Since you specify that you only want to know if the array contains duplicate names and not that you want to know what those duplicate names are, a VBA alternative to #2 would be to do something like this (or a modified version thereof:

Sub Example()
    Dim varArrayToCheck As Variant
    Dim i As Integer, colTemp As Collection
    
    varArrayToCheck = Array("Jiura", "Colin", "Koolsid", "Simon", _
                            "Peter", "Ben", "Phillip", "Ian", "Jiura")
    
    Set colTemp = New Collection
    
    On Error Resume Next
    For i = LBound(varArrayToCheck) To UBound(varArrayToCheck)
       colTemp.Add varArrayToCheck(i), varArrayToCheck(i)
    Next i
    
    On Error GoTo 0
    If colTemp.Count <> UBound(varArrayToCheck) + 1 Then
        MsgBox "Array contains a duplicate name"
    Else
        MsgBox "Array does not contain a duplicate name"
    End If
    
End Sub