LookUp Value and Concatenate All Found Results

**snb** · 07-04-2012, 07:13 PM

Hi Rick,

I was wondering whether this code could perform the same task (or am I overloooking something ?)

Code:

Function search_con_snb(sp As Range, c01 As String)
    sn = sp.Value
    For j = 1 To UBound(sn)
        c02 = c02 & "|" & Join(Application.Index(sn, j), "|")
    Next
    search_con_snb = Join(Filter(Split(c02, "|"), c01), "|")
End Function

**Rick Rothstein** · 07-04-2012, 08:50 PM

Originally Posted by snb

I was wondering whether this code could perform the same task (or am I ovrloooking something ?)

Code:

Function search_con_snb(sp As Range, c01 As String)
    sn = sp.Value
    For j = 1 To UBound(sn)
        c02 = c02 & "|" & Join(Application.Index(sn, j), "|")
    Next
    search_con_snb = Join(Filter(Split(c02, "|"), c01), "|")
End Function

sp is the range to look in
c01 is the string to be looked for.
| is the concatenation delimiter

Our functions are quite different from each other. My function looks in a row or column and, if it finds a match, can return concatentations of values from a different range (sort of like how SUMIF works, but with text and concatenation rather than numbers and summation). Within that functionality, the UDF allows the user to control whether the search is case sensitive or not and whether the search should match the entire contents of the cells it searches or just find the text somewhere within the cell. It also allows you to filter the return list so that all items are returned from the ReturnRange or so that repeated items are listed only once. Also, the function allows the delimiter to be set by the user rather than hard-coding it into the body of the function.

**jeffwtn** · 12-10-2014, 10:25 PM

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.jpg Pictures 2.jpgNAME?

anyway of fixing this ?
Many thanks

**Rick Rothstein** · 12-10-2014, 10:36 PM

Originally Posted by jeffwtn

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.jpg Pictures 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?

**jeffwtn** · 01-11-2015, 09:36 PM

Originally Posted by Rick Rothstein

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.

**Rick Rothstein** · 01-16-2015, 02:04 AM

Originally Posted by jeffwtn

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.

**jeffwtn** · 01-16-2015, 03:03 AM

Originally Posted by Rick Rothstein

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

**Rick Rothstein** · 01-16-2015, 03:26 AM

Originally Posted by jeffwtn

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".