Replace MATCH with more efficient formula

[fboehlandt]

Hi everyone

I would like to check if a specific name in a column of consecutive names has appeared already. The below formula does exactly what I am looking for:

=IF(ISERROR(MATCH(T2,$T$1:T1,0)),"","Existing Member")

when copied to the last cell adjacent to the column containing the names. There are roughly 75000 names to check so performance becomes an issue. Is there any more efficient way to do this besides using MATCH?

Thanks

 

[Aladin Akyurek]

=ISNUMBER(MATCH(T2,$T$1:T1,0))+0

1 = a hit, i.e. an existing member, 0 = a miss, i.e. ok.

This should be a tad faster.

 

[RoryA]

Can you sort the data on the name column?

 

[fboehlandt]

Thanks, both great answers. I managed to sort the data in a custom function, assign flag and re-order according to initial ranking

Function FindShared(MyRange As Range)


Dim n As Long
n = MyRange.Count
ReDim MyArr(1 To n, 1 To 3) As Variant


For i = 1 To n
    MyArr(i, 1) = MyRange(i)
    MyArr(i, 2) = i
    MyArr(i, 3) = MyArr(i, 1) & Application.Text(MyArr(i, 2), "000000")
Next i


'sort by membername
QuickSortArray MyArr, , , 3


MyArr(1, 3) = ""
For i = 2 To n
    If MyArr(i, 1) = MyArr(i - 1, 1) Then
        MyArr(i, 3) = "SharedMember"
    Else
        MyArr(i, 3) = ""
    End If
Next
    
'sort per rank (initial)
QuickSortArray MyArr, , , 2


FindShared = Application.Index(MyArr, 0, 3)


End Function

I used Nigel Heffernan's quick sort for the array