Page 5 of 5 FirstFirst ... 345
Results 41 to 49 of 49

Thread: LookUp Value and Concatenate All Found Results

  1. #41
    Junior Member
    Join Date
    Oct 2016
    Posts
    1
    Rep Power
    0
    Hi Rick,

    Firstly, thanks for sharing this helpful code.

    I have two questions, so I appreciate if you can help me.

    1. Is it possible to skip duplicate results?

    2. I have some empty cells; is it possible to skip them in the result.

    Rick.jpg

  2. #42
    Junior Member
    Join Date
    Oct 2016
    Posts
    2
    Rep Power
    0
    Hi Rick,

    I saved my workbook as macro enabled and saved your UDF code as a module. However when I enter the formula into my table I get the following error: Compile Error. Unexpected end function.

    Is there a way to trouble shoot this?

    Capture.jpg


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 10-24-2023 at 02:54 PM.

  3. #43
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by rwoodsum View Post
    I saved my workbook as macro enabled and saved your UDF code as a module. However when I enter the formula into my table I get the following error: Compile Error. Unexpected end function.

    Is there a way to trouble shoot this?

    Capture.jpg
    It is hard to see that small picture, but if what is displayed in the picture is all of the code you copied, then you did not manage to copy all of the code there is.

  4. #44
    Junior Member
    Join Date
    Oct 2016
    Posts
    2
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    It is hard to see that small picture, but if what is displayed in the picture is all of the code you copied, then you did not manage to copy all of the code there is.
    Oh gosh I didn't even see that the original code has a scroll bar

  5. #45
    Junior Member
    Join Date
    Aug 2017
    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
    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.
    I have installed this as instructed however, the result is just a blank cell. I'm using Excel 2016 on Mac if that makes a difference. I'm really struggling to get this to work I'm new to VBA as well which probably doesn't help :/

    I am unable to attach a sample workbook because the file is too big. So instead, I have included a link below. The second sheet contains a calendar and I would like each day to be filled with the companies from the first sheet where the day matches.

    Also, is it possible to set the font colour of each concatenated result depending on its status in the first sheet? i.e. if a company has a status of UNCONFIRMED, would it possible to make that company and that company only red in the concatenated cell?

    Lastly, on the calendar sheet, how would I fill the cell that corresponds to the current day?

    Link to sample:
    https://drive.google.com/open?id=0By...nBreTlUQ0hCMmM

    Cheeeeers!

  6. #46
    Junior Member
    Join Date
    Nov 2017
    Posts
    1
    Rep Power
    0
    Please forgive me if this is duplicated - my first time using these forums.
    I need to either create a CONCATENATE formula or a VBA to accomplish what is in this picture.Exel Concatenate.JPG

    Thank you!

  7. #47
    Junior Member
    Join Date
    Dec 2017
    Posts
    1
    Rep Power
    0
    I just wanted to take a minute and thank you for this UDF. This solves so many issues I have encountered.

  8. #48
    Junior Member
    Join Date
    Sep 2016
    Posts
    3
    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
    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.
    Hi Rick, such a god-sent! This VBA is marvelous

    Can I follow this up by asking if this can be a lookupconcat UDF for multiple criteria?

  9. #49
    Junior Member
    Join Date
    Oct 2019
    Posts
    1
    Rep Power
    0
    Hi Rick. This is just the thing I was looking for!

    One thing I would like to know is, I would like to exclude blanks from my results. Should I show this in the VBA or in the formula?

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
  •