Creating a String of Cells

D

Dave R.

Here is one way with formulas. It takes a minute to setup...

Take your company names and create a list of the unique entries (for your
example that would include ABC and EFG). You can do this with formulas, or
use data>filter>advanced filter > unique records only, copy and paste them
elsewhere, then data>filter>show all

With your NEW list in A16 to A17, and your old list in A2:B8 (I added a
couple more emails to test)..

Go sort your original list A2:B8 by company name, so that all emails from
the same company are in rows next to eachother.

In B16, put
=IF(COLUMN()-2<COUNTIF($A$2:$A$8,$A16),INDEX($B$2:$B$8,MATCH($A16,$A$2:$A$8,
0)+COLUMN()-2),"")

i.e., A16 contains ABC.

The formula in B16 will return the first email from company ABC. Copy B16
and copy it down for as many companies you have in your unique list, and
copy it far enough to the right that you will not miss any email addresses.

I have a spreadsheet with this already done, let me know if you want a copy.
 
F

Frank Kabel

Hi Dave
but if I understood the OP correctly he wants them all in ONE cell (of
course I could be wrong with that :))
 
D

Dave R.

Oh yes I forgot about that part. Sometimes as you know if he want to use
formulas, there is some extra time involved!

To the OP: you can concatenate these like =B16&C16&D16&E16

etc.
 
C

carl

Thank You Dave and Frank.

Frank is correct that I am trying to find an efficient way to concatenate
the email address into one cell based on the unique company name that is in
column A of the 2nd table.

I would like to try a VB solution. Any suggestions on a starting point ?

Thank you in advance.
 
Top