Creating a String of Cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table of data like so:

CompanyName Email
ABC (e-mail address removed)
ABC (e-mail address removed)
EFG (e-mail address removed)
EFG (e-mail address removed)

Is it possible to create the following table ?

CompanyName EmailString
ABC (e-mail address removed),[email protected]
EFG (e-mail address removed),[email protected]

Thank you in advance.
 
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.
 
Hi Dave
but if I understood the OP correctly he wants them all in ONE cell (of
course I could be wrong with that :-))
 
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.
 
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.
 
Back
Top