Lookup multiple values concatenated into one cell without duplicates

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.

Hi.. Thank you for your quick response. I tried the code but could not follow it properly. It would be great if you can attach a sample file. I have also attached a data file which will better explain what I require.