Frontendplace Blog

Sharing Frontend developing ideas

Vlookup and concat in excel

Posted by info on December 4th, 2011

Rationale:

I want to find all the objects where a specific part is used in a list of objects and show the objectlist so I know in which objects the part is used.
For example I have this table
B 1
A 2
B 3
B 4

how to display the B result like this
B 1;3;4 whereas the vlookup only return 1 value

Solution: use this macro in excel.

' Lookup_concat module
' This module vertical search all occurrences of a string in a column and
' return the values from a cell in an other column in the same
' row separated with a given separator string.
'
' ©2011 www.frontendplace.nl
'
' Requirements:
' A table sorted on the column where the Search_in_col string is
'
' Usage:
' lookup_concat(value to search{Cell|String}, search column{Range}, returned value column{Range}, seperator{String})
'
' Example:
' lookup_concat(A4,'ReferenceSheet'!$B$1:$B$100,'ReferenceSheet'!$A$1:$A$100,";")
'
' Return value:
' found values separated with separator string defaulted with ","
Function Lookup_concat(Search_String As String, Search_in_col As Range, Return_val_col As Range, ByVal Seperator As String)

Dim lRowIndex As Long
Dim strResult As String
Dim strSep As String

If Len(Seperator) = 0 Then strSep = ", " Else strSep = Seperator

For lRowIndex = 1 To Search_in_col.Count
    If InStr(1, Search_in_col.Cells(lRowIndex, 1), Search_String) Then
        If Len(strResult) = 0 Then
            strResult = Return_val_col.Cells(lRowIndex, 1).Value
        Else
            strResult = strResult & strSep & Return_val_col.Cells(lRowIndex, 1).Value
        End If
    End If
Next

Lookup_concat = Trim(strResult)

End Function

Example