How to modify VBA code to stop returning duplicate values

[Shivi0189]

The data is in the form:

1 a
1 b
1 c
2 d
3 e
1 f
2 g
3 h
2 i

For grouping all the values against unique entries, a,b,c,f in front of 1, d,g,i, in front of i and so on, i have following VBA code:

Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
Dim r As Range
Dim Result As String
Result = ""
For Each r In lookuprange
If r = lookupval Then
Result = Result & " " & r.Offset(0, indexcol - 1)
End If
Next r
MYVLOOKUP = Result
End Function

then type: ==MYVLOOKUP(A2,A2:A521,2)

The above is returning duplicate values, how to avoid that? And how to place , between entries?

 

[Eric W]

Try:

Public Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
Dim r As Range, MyDict As Object, i As Variant


    Set MyDict = CreateObject("Scripting.Dictionary")
    For Each r In lookuprange
        If r = lookupval Then MyDict(CStr(r.Offset(, indexcol - 1))) = 1
    Next r
    For Each i In MyDict
        MYVLOOKUP = MYVLOOKUP & ", " & i
    Next i
    MYVLOOKUP = Mid(MYVLOOKUP, 3)
        
End Function

You may want to add the

Application.Volatile

line as well if you want it to update if the table changes.