LookUp Value and Concatenate All Found Results

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

**mickyd** · 07-17-2012, 05:57 AM

Hi Rick,

I was looking at your code and it works perfectly except I was hoping to modify it a little to help me which a problem which I am having. Here's a little information regarding my problem.

I have a workbook which gives me a list of email addresses which are in no order at all, this data is laced in Sheet in Column A . eg.

Firstname1.lastname1(a)domain.com
firstname2.lastname2(a)domain.com
firstname3.lastname1(a)domain.com
Firstname1.lastname2(a)domain.com
firstname3.lastname2(a)domain.com
firstname2.lastname1(a)domain.com

What i am trying to do is search lastname1 and it display Firstname1.lastname1(a)domain.com, firstname2.lastname1(a)domain.com, firstname3.lastname1(a)domain.com.

Is there any way to change the code to allow me to do this?

**Rick Rothstein** · 07-17-2012, 07:49 AM

Originally Posted by mickyd

Hi Rick,

I was looking at your code and it works perfectly except I was hoping to modify it a little to help me which a problem which I am having. Here's a little information regarding my problem.

I have a workbook which gives me a list of email addresses which are in no order at all, this data is laced in Sheet in Column A . eg.

Firstname1.lastname1(a)domain.com
firstname2.lastname2(a)domain.com
firstname3.lastname1(a)domain.com
Firstname1.lastname2(a)domain.com
firstname3.lastname2(a)domain.com
firstname2.lastname1(a)domain.com

What i am trying to do is search lastname1 and it display Firstname1.lastname1(a)domain.com, firstname2.lastname1(a)domain.com, firstname3.lastname1(a)domain.com.

Is there any way to change the code to allow me to do this?

**mickyd** · 07-17-2012, 11:30 AM

**Rick Rothstein** · 07-17-2012, 01:44 PM

Originally Posted by mickyd

Just one last request if you don't mind, would there be any way to change the UDF to display the list on separate line within the same cell instead of separating them with ", ". i've just noticed that some cells have up to 40 emails in it and it would be easier to understand if it was like that.

Sure, all we have to do is replace the ", " delimiter with the Line Feed character. Since the Line Feed character has an ASCII code of 10, we can use CHAR(10) for the delimiter...

=LookUpConcat(".lastname1",A1:A6,A1:A6,CHAR(10),FA LSE,,FALSE)

Don't forget to turn "Wrap text" on for the cell you put this formula in. You will also have to widen the column enough so that the individual lines do not wordwrap at the right edge of the cell.