Hi,

I have a excel dump of employees with more than half a million rows. I need to lookup this data and return multiple values concatenated into one cell without duplicates. I found out an udf which is working well, but the problem is with such a huge data to lookup and dragging this formula to around thousand rows makes the file very heavy and the file stops responding at times for as long as four hours, coz it takes humongous time to execute the code. The udf that I used was:

Function MLOOKUP(lVal, Rng As Range, lVal_Col_Index As Long, Rslt_Col_Index As Long, Optional tFlag As Boolean) As String
Dim a, x, s, i As Long
a = Rng
With CreateObject("scripting.dictionary")
.comparemode = vbTextCompare
For i = 1 To UBound(a, 1)
If UCase(a(i, lVal_Col_Index)) = UCase(lVal) Then
Select Case tFlag
Case True
s = Format(a(i, Rslt_Col_Index), "hh:mm")
If Not .exists(s) Then .Add s, Nothing
Case Else
s = a(i, Rslt_Col_Index)
If Not .exists(s) Then .Add s, Nothing
End Select
End If
Next
If .Count > 0 Then
MLOOKUP = Join(.Keys, ";")
End If
End With
End Function

Please help with something that works fast.