Results 1 to 3 of 3

Thread: Upgrade to LookUpConcat UDF: Search Multiple Criteria and concatenate.

  1. #1
    Junior Member
    Join Date
    Apr 2014
    Posts
    1
    Rep Power
    0

    Upgrade to LookUpConcat UDF: Search Multiple Criteria and concatenate.

    The LookUpConcat UDF helped me greatly, so I wanted to contribute this enhanced version.

    This version works like LookUpConcat, but allows you to search for multiple criteria and it concatenates all results. The search terms are specified as a comma separated list by default, but you can optionally specify any delimiter.

    Code:
    Function Multi_LookUpConcat(ByVal SearchList As String, SearchRange As Range, ReturnRange As Range, _
                          Optional SearchListDelimiter As String = ",", _
                          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
      
    'Parse the SearchList into Strings
    ' Spaces next to the delimiters will be ignored
    Dim SearchString As String
    Dim List As String
    Dim C1 As Integer
    Dim C2 As Integer
    
      If StrComp(SearchList, "") = 0 Then
        Multi_LookUpConcat = ""
    
      ElseIf (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
         (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
        Multi_LookUpConcat = CVErr(xlErrRef)
      
      Else
        
        SearchList = SearchList & SearchListDelimiter   'Ensure that it runs at least once
        C1 = 1
        C2 = InStr(C1, SearchList, SearchListDelimiter)
        
        While C2 > 0
            SearchString = Trim(Mid(SearchList, C1, C2 - C1))
    
            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
       
        ' Advance the pointers to search for the next element
        C1 = C2 + 1
        C2 = InStr(C1, SearchList, SearchListDelimiter)
        Wend
        
        Multi_LookUpConcat = Mid(Result, Len(Delimiter) + 1)
      
      End If
      
    End Function

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,106
    Rep Power
    10
    Thanks. Moved to Tips and Tricks section.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Dec 2017
    Posts
    6
    Rep Power
    0
    @DJE, I've attached below an excel file where I used your VBA code above.
    In F1 ... H4 cells I would like name lists (separated by comma) with the values from column C taking into account the two criteria listed in the cells: F1, G1, H1 and E2...E4.
    Attached Files Attached Files

Similar Threads

  1. Concatenate Two Or Multiple Columns In To One
    By william516 in forum Excel Help
    Replies: 10
    Last Post: 07-06-2013, 12:39 AM
  2. Question on UDF LookUpConcat
    By K2` in forum Excel Help
    Replies: 4
    Last Post: 05-07-2013, 10:55 PM
  3. Concatenate multiple values
    By mcpizzle in forum Excel Help
    Replies: 3
    Last Post: 08-30-2012, 04:33 PM
  4. Replies: 4
    Last Post: 08-11-2012, 11:20 PM
  5. Concatenate Multiple Lookup Values in Single Cell
    By Admin in forum Download Center
    Replies: 4
    Last Post: 04-06-2012, 09:37 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •