Results 1 to 4 of 4

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

Hybrid View

Previous Post Previous Post   Next Post Next Post
  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,123
    Rep Power
    10
    Thanks. Moved to Tips and Tricks section.

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg
    https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I
    https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M
    ttps://www.youtube.com/watch?v=LP9fz2DCMBE
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8
    ttps://www.youtube.com/watch?v=bFxnXH4-L1A
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
    ttps://www.youtube.com/watch?v=GqzeFYWjTxI
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 11-30-2023 at 02:52 PM.
    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

  4. #4
    Junior Member
    Join Date
    May 2018
    Posts
    1
    Rep Power
    0

    Not working for multiple

    Quote Originally Posted by pub View Post
    @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.
    Hi,

    I tried the multiple lookup_Concat and I get a REF! error

    Given the same data in the example XLSX file, I used this formula in cell F3:

    =Multi_LookUpConcat("F1;E3",$A$2:$B$50,$C$2:$C$50,";",", ")

    F1 and E3 are the two criteria to search for.
    $A$2:$B$50 are the columns these data should be found.
    $C$2:$C$50 is the range to pull results from

    I can only successfully concat with one criteria. Any help would be appreciated.

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:09 AM
  2. Question on UDF LookUpConcat
    By K2` in forum Excel Help
    Replies: 4
    Last Post: 05-07-2013, 10:25 PM
  3. Concatenate multiple values
    By mcpizzle in forum Excel Help
    Replies: 3
    Last Post: 08-30-2012, 04:03 PM
  4. Replies: 4
    Last Post: 08-11-2012, 10:50 PM
  5. Concatenate Multiple Lookup Values in Single Cell
    By Admin in forum Download Center
    Replies: 4
    Last Post: 04-06-2012, 09:07 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
  •