LookUp Value and Concatenate All Found Results

[Randall Kern]

I just wanted to take a minute and thank you for this UDF. This solves so many issues I have encountered.

[herman925]

The idea for this UDF (user defined function) came from this article which I saw in another forum...

Excel udf: Lookup and return multiple values concatenated into one cell | Get Digital Help - Microsoft Excel resource

This is actually an old article that someone just commented on which, in turn, brought it to the top of that forum's "Recent Comments" list and, hence, to my attention. When I looked at the article, I decided the UDF presented by that programmer could be expanded to make it more flexible, hence this current article by me (you should follow the above link in order to see the type of data being processed and the expected resulting output from it). Here is the code I came up with...

Function LookUpConcat(ByVal SearchString As String, _
                      SearchRange As Range, _
                      ReturnRange As Range, _
                      Optional Delimiter As String = " ", _
                      Optional MatchWhole As Boolean = True, _
                      Optional UniqueOnly As Boolean = False, _
                      Optional MatchCase As Boolean = False)
                   
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String
  
  If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
     (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
    LookUpConcat = CVErr(xlErrRef)
  Else
    If Not MatchCase Then SearchString = UCase(SearchString)
    For X = 1 To SearchRange.Count
      If MatchCase Then
        CellVal = SearchRange(X).Value
      Else
        CellVal = UCase(SearchRange(X).Value)
      End If
      ReturnVal = ReturnRange(X).Value
      If MatchWhole And CellVal = SearchString Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
                ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
                ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      End If
Continue:
    Next
    
    LookUpConcat = Mid(Result, Len(Delimiter) + 1)
  End If
  
End Function

The first three arguments are required. The first argument is the text you want to search for. The second argument is the range to be searched... this range can either be a column of cell references or a row of cell reference (it cannot be a two-dimensional array of cells though). The third argument is the range from which the matching results will be taken from... this range can be a column of cell references or a row of cell reference (it cannot be a two-dimensional array of cells though). Note that while normally the orientation of the search and result ranges will be the same (easier to coordinate between the two I would think), this is not a requirement... one can be a reference to a row of cells and the other can be a reference to a column of cells.

The remaining arguments are all optional. The fourth argmument, a String, is the text (one or more characters) that you want to be the delimiter between the concatenated text that is found during the lookup (the default value is a space character). The fifth argument, a Boolean, determines whether the lookup should be trying to match the entire cell value or only part of the cell value (the default value is True meaning the lookup will have to match the entire cell value). The sixth argument, a Boolean, determines if the concatenated list that is returned should allow duplicated values to be listed or whether a found value should only be listed one time, that being the first occurrence in the range (the default value is False meaning found values will be listed as many times as they are found within the search range). The seventh argument, a Boolean, determines whether the lookup process will be case sensitive or not (the default value is False meaning the searched for text does not have to be of the same case as the text being searched).

That is pretty much it. Hopefully my explanation has been clear enough but, if not, please feel free to ask me go into more detail on any parts that seem "murky" to you.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use LookUpConcat just like it was a built-in Excel function.

Hi Rick, such a god-sent! This VBA is marvelous

Can I follow this up by asking if this can be a lookupconcat UDF for multiple criteria?

[Brooke.Zammit]

Hi Rick. This is just the thing I was looking for!

One thing I would like to know is, I would like to exclude blanks from my results. Should I show this in the VBA or in the formula?

[Rick Rothstein]

You are correct in your interpretation of what I am doing, and in any other case I would prefer your suggested output, but in this particular case I need the output in every cell.

Okay, here's the solution. Rather than create a new procedure, I simply wrote a macro that repeatedly calls my LookUpConcat function (while it can be used as a UDF, it is also a perfectly good VB function as well). So, put the LookUpConcat function in a module and then put this macro in the same module...

Sub LookUpAndConcatenate()
  Dim Cell As Range, LastRow As Long, ResultRow As Long
  Dim SearchRange As Range, ReturnRange As Range
  Const SearchCol As String = "B"
  Const ReturnCol As String = "AU"
  Const ResultCol As String = "D"
  Const StartRow As Long = 3
  LastRow = Cells(Rows.Count, SearchCol).End(xlUp).Row
  Set SearchRange = Cells(StartRow, SearchCol).Resize(LastRow - StartRow + 1)
  Set ReturnRange = Cells(StartRow, ReturnCol).Resize(LastRow - StartRow + 1)
  For Each Cell In SearchRange
    Cells(Cell.Row, ResultCol).Value = LookUpConcat(Cell.Value, SearchRange, ReturnRange)
  Next
End Sub

Then, when you want to fill the cells, just run the above LookUpAndConcatenate macro. Note that I used Const statements (they declare constants) to set the various parameters from your worksheet so that you can change them later on in case things change on your worksheet for some reason.

[Rick Rothstein]

Thank you, this works brilliantly - being able to adjust the three column names turned out very useful in the end as I needed to change the output destination.

The macro takes a bit longer to run than just using the UDF on the column repeatedly, but the result is far easier to work with and the spreadsheet is more stable.

You are quite welcome... I am glad everything ended up working out for you.