Page 4 of 5 FirstFirst ... 2345 LastLast
Results 31 to 40 of 49

Thread: LookUp Value and Concatenate All Found Results

  1. #31
    Junior Member
    Join Date
    Jan 2015
    Posts
    1
    Rep Power
    0

    Find matches in multiple columns

    Hi Rick,

    Thanks for this code. Would it be possible to modify it to find a combination of strings in different columns? I have attached my example spreadsheet in case it makes it easier.

    lookup_test.xlsm

    Basically, I want my users to select values from three different dropdowns (B2:B4 in my example). Based on these selections I'd like the function to determine which combo of columns H-J fits and return the corresponding value from G.

    Perhaps I'm barking up the wrong tree?

    Thanks so much!

    Matt

  2. #32
    Junior Member
    Join Date
    Dec 2014
    Posts
    3
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    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?
    Sample attached. Thanks for your help.
    Attached Files Attached Files

  3. #33
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by jeffwtn View Post
    Sample attached. Thanks for your help.
    I think Admin posted the solution for you in Message #32... you need to save the workbook as an "Excel Macro-Enabled Workbook (*.xlsm)" and NOT as plain "Excel Workbook (*.xlsx)". When you save the workbook with the macro code in place, click the drop-down under the "File name" field (it is labeled "Save as type:") and select what should be the second item in the list (with the wording I show above). After that, when you reopen the workbook, you will have to click the button to enable the macro code.

  4. #34
    Junior Member
    Join Date
    Dec 2014
    Posts
    3
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    I think Admin posted the solution for you in Message #32... you need to save the workbook as an "Excel Macro-Enabled Workbook (*.xlsm)" and NOT as plain "Excel Workbook (*.xlsx)". When you save the workbook with the macro code in place, click the drop-down under the "File name" field (it is labeled "Save as type:") and select what should be the second item in the list (with the wording I show above). After that, when you reopen the workbook, you will have to click the button to enable the macro code.
    Hi Thanks,

    I have retry to do it , still not joy. At first even .xlsx working fine but after a while it just does not work anymore
    file attached. Thanks again



    Jeff
    Attached Files Attached Files

  5. #35
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by jeffwtn View Post
    I have retry to do it , still not joy. At first even .xlsx working fine but after a while it just does not work anymore
    file attached. Thanks again
    You have the code installed in the wrong module. Delete what you now have and then go back to Message #1 and read the instructions at the bottom of the article labeled "HOW TO INSTALL UDFs".

  6. #36
    Junior Member
    Join Date
    Apr 2015
    Posts
    1
    Rep Power
    0
    Hi Rick. Great UDF! Thanks much!

    The formula seems to fall over for me when the SearchRange is > 44k rows and I'm actually trying to compare against a 180+k rows.
    Is there a possible workaround?

  7. #37
    Junior Member
    Join Date
    May 2015
    Posts
    1
    Rep Power
    0

    Post @Rick Rothstein

    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,
    thanks for your help on this topic. I don't know how to write or interpret VBA all together but I have downloaded your sample and seems like its working when I move my own data into it.
    Except, I am looking for 2 items in a separate tab and using the vlookup,choose formula together.
    so the formula goes and look for a date and project number in a different tab and bring the match back but only the first match and ignore the rest.
    However, your VBA is bringing all the matching items back but only from one selected cell. I tried to add if function or the vlookup match function to your vba formula but didn't work .
    any suggestion? Appreciate your help and support.

  8. #38
    Junior Member
    Join Date
    Sep 2016
    Posts
    3
    Rep Power
    0
    Rick, this is digging up old posts, I know. But this VB really saved me so much trouble.

    I do have one need from this code though, that is, to add 'and' for the last two sets to variables looked up by the code.

    Basically, I'm using your code to make a mail merge section for my Word, which is a contract for family signing, so, by looking up Reference numbers in the MasterList, your code helps me generate all the values and complies it into one cell, where I can easily merge to my contract form.

    The issue is that I'd like to have 'and' for the last two person's names, which is an essential thing because it is a standardised format of the contract....so if I have 3 names, for example, Michael Jones, Amy Jones, Lincoln Jones, I'd like the cell to display 'Michael Jones, Amy Jones, and Lincoln Jones'

    Is there a code that helps detect that it is the last entry and thus add prefix to the final variable?

  9. #39
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by herman925 View Post
    I do have one need from this code though, that is, to add 'and' for the last two sets to variables looked up by the code.
    Here is the code where I replaced the last line of code before the last "End If" with two lines of code...
    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 Result = Mid(Result, Len(Delimiter) + 1) LookUpConcat = Application.Replace(Result, InStrRev(Result, Delimiter), Len(Delimiter), " and ") End If End Function

  10. #40
    Junior Member
    Join Date
    Sep 2016
    Posts
    3
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    Here is the code where I replaced the last line of code before the last "End If" with two lines of code...
    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 Result = Mid(Result, Len(Delimiter) + 1) LookUpConcat = Application.Replace(Result, InStrRev(Result, Delimiter), Len(Delimiter), " and ") End If End Function
    Hi Rick, thanks for the help. I do still have some trouble with this code.

    It will produce the word 'and' for even cells that only contain 1 name.

    So for my example I gave previously, it worked well. However for cells that only contains 1 name, for example 'Rick Rothstein', it will become 'Rick and Rothstein'

    Is it possible to make the rule (I'm, completely, a noob, on VB) such that for cells containing more than 1 value, the final 2 values can be separated by 'and' while others by comma only.

    so:
    (for 1 value) James Jones
    (for 2 values) James Jones and Lillian Jones
    (for 3 values) James Jones, Lillian Jones and Michael Jones

    Sorry for the trouble, thank you.

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
  •