LookUp Value and Concatenate All Found Results

**Rick Rothstein** · 07-25-2012, 07:34 PM

Originally Posted by mickyd

...how do i implement the Marco instead of the UDF....

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, 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. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (LookUpAndConcatenate) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. To make things simpler for you (especially if you will need to call this macro often), when you select the macro from the list, before clicking Run, you can click the Options button and assign a keyboard shortcut to it first and then use that keyboard shortcut the next time you want to run the macro.

**Admin** · 05-08-2013, 09:15 AM

Guys, Please ask the questions related this UDF at http://www.excelfox.com/forum/f2/que...ncat-950/]here

**poptcorn** · 03-11-2014, 02:42 AM

I have tried tinkering with this to make it skip cells with "" - but I have had no luck. Is there an easy workaround for this?

**smr528** · 06-27-2014, 09:10 PM

**Agent100** · 11-17-2014, 08:29 PM

Originally Posted by smr528

Rick,

Is there a way to integrate the multiple criteria lookup_concat function created by Oscar with specifically the functionality to remove duplicates?

Thank you in advance for your help.

Has this been responded to? I have the same predicament that I am trying to solve for. In short, the lookup_Concat UDF works very well for validating a single criterion and returning the concatenated results into a single cell. However, in my particular use case, I have 2 criterion that require validation prior to returning the concatenated results. This would be equivalent to an "=if(and(" type function. Is this possible, and if so, any assistance in how to properly operate it would be very much appreciated.

Thanks in advance - and nice work on the UDF as it gets me 90% of the way there!

Agent100

**Agent100** · 11-18-2014, 06:17 PM

Originally Posted by Agent100

Has this been responded to? I have the same predicament that I am trying to solve for. In short, the lookup_Concat UDF works very well for validating a single criterion and returning the concatenated results into a single cell. However, in my particular use case, I have 2 criterion that require validation prior to returning the concatenated results. This would be equivalent to an "=if(and(" type function. Is this possible, and if so, any assistance in how to properly operate it would be very much appreciated.

Thanks in advance - and nice work on the UDF as it gets me 90% of the way there!

Agent100

So after some digging, I eventually found the below VBA. It works, but causes my spreadsheet to operate VERY slowly. Looking to use a macro now instead of calling the UDF in every cell....hopefully I am able to figure that out. In any event, figured it would be a good idea to share the VBA for the multiple criteria UDF. Here goes:

Function Lookup_concat(Search_stringA As String, Search_in_colA As Range, _
Search_stringB As String, Search_in_colB As Range, Return_val_col As Range)

Dim i As Long
Dim result As String

For i = 1 To Search_in_colA.Count
If Search_in_colA.Cells(i, 1) = Search_stringA And _
Search_in_colB.Cells(i, 1) = Search_stringB Then
result = result & " " & Return_val_col.Cells(i, 1).Value
End If
Next i

Lookup_concat = Trim(result)

End Function

The formula syntax in the cell would be:

=lookup_concat(cell of first value,'Sheet Name'!range:range,cell of second value,'Sheet name'!range:range,'Sheet name'!return results range: return results range)

For instance:
=lookup_concat(E$3,'Consolidated Data'!$I:$I,$B5,'Consolidated Data'!$E:$E,'Consolidated Data'!$A:$A)

Again, just trying to share what I have learned. Hopefully it helps others who are digging for the solution of multiple criteria. I will update if/when I am able to effectively write a macro to call this UDF vs. using the UDF (again, causes my workbook to perform SUPER slowly).

Cheers

**RobertBolin** · 12-10-2014, 09:35 PM

Originally Posted by Agent100

So after some digging, I eventually found the below VBA. It works, but causes my spreadsheet to operate VERY slowly. Looking to use a macro now instead of calling the UDF in every cell....hopefully I am able to figure that out. In any event, figured it would be a good idea to share the VBA for the multiple criteria UDF. Here goes:

Function Lookup_concat(Search_stringA As String, Search_in_colA As Range, _
Search_stringB As String, Search_in_colB As Range, Return_val_col As Range)

Dim i As Long
Dim result As String

For i = 1 To Search_in_colA.Count
If Search_in_colA.Cells(i, 1) = Search_stringA And _
Search_in_colB.Cells(i, 1) = Search_stringB Then
result = result & " " & Return_val_col.Cells(i, 1).Value
End If
Next i

Lookup_concat = Trim(result)

End Function

The formula syntax in the cell would be:

=lookup_concat(cell of first value,'Sheet Name'!range:range,cell of second value,'Sheet name'!range:range,'Sheet name'!return results range: return results range)

For instance:
=lookup_concat(E$3,'Consolidated Data'!$I:$I,$B5,'Consolidated Data'!$E:$E,'Consolidated Data'!$A:$A)

Again, just trying to share what I have learned. Hopefully it helps others who are digging for the solution of multiple criteria. I will update if/when I am able to effectively write a macro to call this UDF vs. using the UDF (again, causes my workbook to perform SUPER slowly).

Cheers

First, for multiple columns, simple create a third column with the concatenation of the two. So the search string in your query would be E$3&$B5 and on Consolidated Data, you would put in column P (or some such) the values of column I and column E (i.e. =$I1&$E1 would go in P1 and copy it down). Then you'd just code the use 'Consolidated Data'!$P:$P for the search range.

OK, for those wanting a version that removes blanks, the following is a cleaned up (got rid of this bad "GoTo" calls) that has an additional flag to suppress blanks.

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, Optional SupressBlanks 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) Or _
     (SearchRange.Count <> ReturnRange.Count) Then
    LookUpConcat = "CVErr(xlErrRef)"
  Else
    Result = ""
    If Not MatchCase Then
        SearchString = UCase(SearchString)
    End If
    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 (Not SupressBlanks) Or Trim(ReturnVal) <> "" Then
        If MatchWhole And CellVal = SearchString Then
          If (Not UniqueOnly) Or InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) < 1 Then
            If Trim(Result) > "" Then Result = Result & Delimiter
            Result = Result & ReturnVal
          End If
        ElseIf (Not MatchWhole) And CellVal Like "*" & SearchString & "*" Then
          If (Not UniqueOnly) Or InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) < 1 Then
            If Trim(Result) > "" Then Result = Result & Delimiter
            Result = Result & ReturnVal
          End If
        End If
      End If
    Next
    LookUpConcat = Result
  End If
End Function

**Alaeddine** · 08-14-2014, 06:01 PM

Originally Posted by Rick Rothstein

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

hello sir please is there anyway so we can put a comma in between the concatenated results
Thank you

**Rick Rothstein** · 08-17-2014, 09:33 PM

Originally Posted by Alaeddine

hello sir please is there anyway so we can put a comma in between the concatenated results

There is an optional Delimiter argument (it is the fourth argument) that allows you do specify whatever character or characters you want to be the delimiter.