How to concatenate email addresses from different rows into 1 line with comma separator

[ohlalayeah]

I have a list like below

NAME	Email
Peter	[email protected]
Peter	[email protected]
Mary	[email protected]

How can I make it to like below?

Peter	[email protected],[email protected]
Mary	[email protected]

Thanks!

[djm601]

How many rows are we talking? The simplest way would be to put a filter on the name column, select the name you desire, then in the adjacent column have something like -

Please Login or Register  to view this content.

where B2:b3 is your range of email addresses for Peter.

then repeat for the remaining names.

I'll think of a quicker way in a minute

[ohlalayeah]

I have more than 10000 rows, any better way?

[ron2k_1]

by code, go here:
http://www.cpearson.com/Excel/stringconcatenation.aspx

by formula I'd do it like this (Assuming emails start from A1):
1. In B1 insert =A1
2. In B2 insert=B1&","&A2
3. Drag down B2 as far as needed

[ohlalayeah]

Thanks, but I need a formula that can automatically concatenate email addresses that with same name under "Name" column. I have more than 10000 rows of email address in which some are belonged to same people.

[djm601]

There's probably a simpler way but this works.

This involves a few steps but I've achieved what your after.

Assuming your list of names are in column A and the email addresses are in column B

First copy the list of names and remove any duplicates, fairly straight forward.

Then transpose the list of names (horizontally) starting at E1. Under the first name do a VLOOKUP

Please Login or Register  to view this content.

where E$1 is your first name in the horizontal list (my short list of email addresses run from B1:B6, yours will probably be B1:B1000)

This will display the first email address for this person. Drag the formula down until you begin to see #N/A (we can remove these later along with the duplicates.)
repeat the process for the long list of names by dragging the formula underneath each name and down.

Now you have all the email address for each person below their name. Copy the entire range to a separate sheet and paste special (Values) and remove all formatting and duplicates.

Now for the concatenation.

Below the first column of names in an empty cell type:

Please Login or Register  to view this content.

before you press enter, press F9. (you'll see the list of names in the formula bar.) Delete the curly brackets{} from the start and the end, and also the last comma on the last email address. Then enclose the string in normal brackets() and type concatenate before the first bracket like this

Please Login or Register  to view this content.

Then press enter and you'll be left with this:

Please Login or Register  to view this content.

You should then be able to drag this across for the list of names.

Hope this helps. Just an idea - had a 5 minutes to spare .

Dave

[ohlalayeah]

Thanks, can you put into an excel file and upload here? Then I'll have better understanding .

[djm601]

Book1.xlsx

Here you go.
There are two sheets on the workbook.

[ohlalayeah]

this seems not a simple method. remember I have more than 100000 records, in which there are around 8000 people, in those 8000 people some of them have more than 1 email addresses. Is it to use MATCH function to aggregate the email addresses belonged to same persons and concatenate the addresses in 1 line separated with comma or semi-colon? I attached my sample file in first post