Page 3 of 5 FirstFirst 12345 LastLast
Results 21 to 30 of 49

Thread: LookUp Value and Concatenate All Found Results

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

    Very useful, thanks!

    Super bit of code, saved me all sorts of hassle. Thanks!
    Quote Originally Posted by theladysaphir View Post
    Applying the calculation to the whole column takes about 6 minutes if I just sit back and let it work.
    The time taken to process whole columns gets out of hand if you are running the code on lots of input strings (e.g. down the side of a long table of search strings). It helps a bit to use a COUNTIF to trigger a normal VLOOKUP instead where there is only one value to find, but if all your data has multiple 'hits' then constraining your input ranges is very necessary.

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
    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/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 12-14-2023 at 02:49 AM.

  2. #22
    Junior Member
    Join Date
    Jun 2014
    Posts
    1
    Rep Power
    0

    Multiple Criteria & remove duplicates

    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.

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg. 9fxrOrrvTln9g9wr8mv2CS
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g96yGbAX4t
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g7lhoX-ar5
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg. 9g9wJCunNRa9gJGhDZ4RI2
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugz-pow-E8FDG8gFZ4l4AaABAg.9f8Bng22e5d9f8hoJGZY-5
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxev2gQt7BKZ0WYMfh4AaABAg. 9f6hAjkC0ct9f8jleOui-u
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxg9iT7MPWGBWruIzR4AaABAg
    https://www.youtube.com/watch?v=tzbKqTRuRzU&lc=UgyYW2WZ2DvSrzUKnJ14AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=Ugz39PGfytiMUCmTPTl4AaABAg. 91d_Pbzklsp9zfGbIr8hgW
    https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=UgwbcybM8fXnaIK-Y3B4AaABAg.97WIeYeaIeh9zfsJvc21iq
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zciSZa959d
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zckCo1tvPO
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 02-24-2024 at 08:05 PM.

  3. #23
    Junior Member
    Join Date
    Aug 2014
    Posts
    1
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    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

  4. #24
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Alaeddine View Post
    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.

  5. #25
    Junior Member
    Join Date
    Nov 2014
    Posts
    2
    Rep Power
    0
    Quote Originally Posted by smr528 View Post
    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

  6. #26
    Junior Member
    Join Date
    Nov 2014
    Posts
    2
    Rep Power
    0

    Multiple Validation Criteria

    Quote Originally Posted by Agent100 View Post
    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

  7. #27
    Junior Member
    Join Date
    Dec 2014
    Posts
    1
    Rep Power
    0

    A better designed solution and some helpful advice for using multiple column searches

    Quote Originally Posted by Agent100 View Post
    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

  8. #28
    Junior Member
    Join Date
    Dec 2014
    Posts
    3
    Rep Power
    0
    Hi Rick ,

    I have tried your solution , at first it is all working well, but when I open it again there are some errors

    =LookUpConcat(A2,$A$2:$A$20,$C$2:$C$20,", ")

    A2 is the lookup where as it came out as #VALUE!
    and then the final result came out as NAME?
    Pictures 1.jpgPictures 2.jpgNAME?

    anyway of fixing this ?
    Many thanks

  9. #29
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by jeffwtn View Post
    Hi Rick ,

    I have tried your solution , at first it is all working well, but when I open it again there are some errors

    =LookUpConcat(A2,$A$2:$A$20,$C$2:$C$20,", ")

    A2 is the lookup where as it came out as #VALUE!
    and then the final result came out as NAME?
    Pictures 1.jpgPictures 2.jpgNAME?

    anyway of fixing this ?
    Many thanks
    Assuming there is no sensitive data... can you post a copy of the workbook so I can debug the problem with a live sheet? If there is sensitive data, can you delete it without affecting the problem you are reporting and then post of copy of that modified workbook instead?

  10. #30
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    I guess he might have saved the workbook as .xlsx. Save the workbook either .xlsm or .xlsb.
    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)

Similar Threads

  1. Replies: 5
    Last Post: 06-04-2013, 01:04 PM
  2. Replies: 6
    Last Post: 12-12-2012, 08:03 PM
  3. Lookup lookup lookup just can't make it work
    By work2live in forum Excel Help
    Replies: 1
    Last Post: 12-08-2012, 11:48 PM
  4. Excel found unreadable content
    By zyousafi in forum Excel Help
    Replies: 2
    Last Post: 08-08-2012, 10:41 AM
  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
  •