CODEWITHSUNDEEP
excelvbavlookup
Benny Hill
Benny Hill

Reputation: 19

Excel VLookup with multiple results, but without duplicates

In this question, the top-voted (best) answer written by aevanko (see code below) works, however it does not account for duplicate values - it just includes them multiple times.

How can I tweak this function to only show distinct values/results?

Function VLookupAll(ByVal lookup_value As String, _
                    ByVal lookup_column As range, _
                    ByVal return_value_column As Long, _
                    Optional seperator As String = ", ") As String

Dim i As Long
Dim result As String

For i = 1 To lookup_column.Rows.count
    If Len(lookup_column(i, 1).text) <> 0 Then
        If lookup_column(i, 1).text = lookup_value Then
            result = result & (lookup_column(i).offset(0, return_value_column).text & seperator)
        End If
    End If
Next

If Len(result) <> 0 Then
    result = Left(result, Len(result) - Len(seperator))
End If

VLookupAll = result

End Function

Upvotes: 2

Views: 703

Answers (1)

YowE3K
YowE3K

Reputation: 23974

To avoid adding something to the result string if it already exists in the string, just check to see if it is already in the string before adding it:

Function VLookupAll(ByVal lookup_value As String, _
                    ByVal lookup_column As range, _
                    ByVal return_value_column As Long, _
                    Optional separator As String = ", ") As String

    Dim i As Long
    Dim result As String

    For i = 1 To lookup_column.Rows.count
        If Len(lookup_column(i, 1).text) <> 0 Then
            If lookup_column(i, 1).text = lookup_value Then
                If Instr(separator & result & separator, _
                         separator & lookup_column(i).Offset(0, return_value_column).Text & separator) = 0 Then
                    result = result & lookup_column(i).Offset(0, return_value_column).Text & separator
                End If
            End If
        End If
    Next

    If Len(result) <> 0 Then
        result = Left(result, Len(result) - Len(separator))
    End If

    VLookupAll = result

End Function

This will only work reliably if you use a non-blank separator, and none of your data actually includes the separator.

If you wish to treat different cases of the same value (e.g. "ABC" and "Abc") as a "duplicate", you will need to change the If statement from

If Instr(separator & result & separator, _
         separator & lookup_column(i).Offset(0, return_value_column).Text & separator) = 0 Then

to

If Instr(separator & LCase(result) & separator, _
         separator & LCase(lookup_column(i).Offset(0, return_value_column).Text) & separator) = 0 Then

but your testing for the lookup value (i.e. If lookup_column(i, 1).text = lookup_value Then) is currently case-sensitive, so you probably don't want to have "duplicates" detected by a case-insensitive match.

Upvotes: 2

Related Questions