LookUp Value and Concatenate All Found Results

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

**mickyd** · 07-18-2012, 07:31 AM

That's perfect, thanks a lot Rick.

Only thing I need to work out now is why my spreadsheet keeps crashing on me

.

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

Originally Posted by theladysaphir

Thank you, this works brilliantly - being able to adjust the three column names turned out very useful in the end as I needed to change the output destination.

The macro takes a bit longer to run than just using the UDF on the column repeatedly, but the result is far easier to work with and the spreadsheet is more stable.

You are quite welcome... I am glad everything ended up working out for you.

**mickyd** · 07-25-2012, 04:57 PM

I'm going to sound really dumb...

but how do i implement the Marco instead of the UDF.... My spread sheet is driving me nuts atm.

**Rick Rothstein** · 07-25-2012, 07:34 PM

Originally Posted by mickyd

...how do i implement the Marco instead of the UDF....

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, 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. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (LookUpAndConcatenate) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. To make things simpler for you (especially if you will need to call this macro often), when you select the macro from the list, before clicking Run, you can click the Options button and assign a keyboard shortcut to it first and then use that keyboard shortcut the next time you want to run the macro.