LookUp Value and Concatenate All Found Results

**Rick Rothstein** · 03-27-2012, 12:57 AM

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...

Code:

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.

https://www.youtube.com/@alanelston2330/featured

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=317218#p317218
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=317006#p317006
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

**snb** · 06-09-2012, 07:14 PM

Hi Rick

I'd prefer

Code:

LookUpConcat = Mid(Result, 2)

to

Code:

LookUpConcat = Mid(Result, Len(Delimiter) + 1)

**theladysaphir** · 07-03-2012, 09:47 PM

**Rick Rothstein** · 07-03-2012, 11:15 PM

I am glad you were able to put the UDF in my article to such practical use... thank you so much for letting me know. As for using the absolute reference... you will find that necessary to do whenever referencing a fixed range from within a formula that you copy (drag down) into other cells.

I was just wondering, though, do you really need "live" formulas for what you are doing or would a macro be more practical for you needs? I ask because you are loading up your worksheet with a lot of formulas for what looks to me like a task that only needs to be done infrequently for any give set of data. If a macro would be more useful, let me know and I'll convert the UDF into one for you.

**theladysaphir** · 07-04-2012, 05:24 PM

**Rick Rothstein** · 07-04-2012, 06:44 PM

Originally Posted by theladysaphir

A macro would probably be more useful.

Let me get some setup information from you first then; please answer the following questions...

1. Will the macro always look for its words to match in Column B, or could the column vary from file to file?

2. Will the macro always look in AU for the items to concatenate, or could the column vary from file to file?

3. You show the lookup and concatenate lists going from Row 3 to Row 6941. I am guessing the starting row will always be Row 3, but that last row seems strange. Will it always be 6941 or should the macro simply look for the last row with data in the lookup column?

4. What column should the output go to? Will it always be the same column for all the files you will use the macro against?

**snb** · 07-04-2012, 07:13 PM

Hi Rick,

I was wondering whether this code could perform the same task (or am I overloooking something ?)

Code:

Function search_con_snb(sp As Range, c01 As String)
    sn = sp.Value
    For j = 1 To UBound(sn)
        c02 = c02 & "|" & Join(Application.Index(sn, j), "|")
    Next
    search_con_snb = Join(Filter(Split(c02, "|"), c01), "|")
End Function

**Rick Rothstein** · 07-04-2012, 08:50 PM

Originally Posted by snb

I was wondering whether this code could perform the same task (or am I ovrloooking something ?)

Code:

Function search_con_snb(sp As Range, c01 As String)
    sn = sp.Value
    For j = 1 To UBound(sn)
        c02 = c02 & "|" & Join(Application.Index(sn, j), "|")
    Next
    search_con_snb = Join(Filter(Split(c02, "|"), c01), "|")
End Function

sp is the range to look in
c01 is the string to be looked for.
| is the concatenation delimiter

Our functions are quite different from each other. My function looks in a row or column and, if it finds a match, can return concatentations of values from a different range (sort of like how SUMIF works, but with text and concatenation rather than numbers and summation). Within that functionality, the UDF allows the user to control whether the search is case sensitive or not and whether the search should match the entire contents of the cells it searches or just find the text somewhere within the cell. It also allows you to filter the return list so that all items are returned from the ReturnRange or so that repeated items are listed only once. Also, the function allows the delimiter to be set by the user rather than hard-coding it into the body of the function.

**Rick Rothstein** · 07-12-2012, 07:33 PM

Originally Posted by theladysaphir

1+2. I will set up the instructions for other users so that the data will always be in column B/AU.
3. The macro should simply look for the last row with data in the lookup column.
4. I will set up the instructions so the output will always be column D.

Okay, I started to set things up and I need clarification on one point please. In your earlier message, you said this...

[I'm sure advanced users would do this anyway, but I just wanted to add that I had to lock the ranges when applying the lookup all down the column, or it would not look for the previously searched on values, which in my case I need it to do. This means my formula comes out =LookUpConcat(B3,$B$3:$B$6941,$AU$3:$AU$6941) rather than =LookUpConcat(B3,B3:B6941,AU3:AU6941) which is where I was originally going wrong.

I am gathering from your last message that you put the the above formula in D3 and then copied it down to Row 6941 (the last row of data), is that correct? If so, that means column D will have multiple copies of the same concatenated text; that is, everytime B3 repeats itself in Column B (and there should be repeats or why else would you be using my LookUpConcat function), the concatenated list will be displayed "next to it" in Column D. Did you really want all those repeats? Or would putting the concatenated list next to (in Column D) the first occurrence of the lookup word be enough (leaving Column D's row blank for when the word is repeated later on)? Seems like the latter would be less cluttered and would allow you to find each word's concatentation easier.

**Rick Rothstein** · 07-12-2012, 08:34 PM

Originally Posted by theladysaphir

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...

Code:

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.