lookup/ concatenate/ filter combo for mailing list

[Jaybeerex]

I've been a lurker on this forum for so long and its truly amazing, but I've hit a bit of a wall on this one in excel i'm guessing due to me not being too sure what to look for!

I've a data set of customers along with identifiers, email addresses, reference numbers and a cohort group for a mail send based on various factors

many of these customers email addresses exist multiple times across the same cohort due to having different reference numbers as they have more than 1 service registered under the same email address.

What i wanted ideally is a comma separated output which i can do with concatenate to match 2 reference numbers to 1 email address, but due to the data set 7k+ lines 2.5k unique email addresses, its how best to use a function to get the correct output on a bulk basis?

This way a single email address can reference multiple lines in a single cohort specific email instead of sending multiple emails for each service registered....

 

[JamesCanale]

Office 365 solution:

Mr Excel Playground 3.xlsm
	A	B	C	D	E
1	Email	Service		email	service
2	a@b.com	11		a@b.com	11, 22
3	a@b.com	22		c@b.com	33
4	c@b.com	33		d@e.com	44, 55, 66
5	d@e.com	44
6	d@e.com	55
7	d@e.com	66
Sheet11

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A2:A7)
E2:E4	E2	=TEXTJOIN(", ",TRUE,IF($A$2:$A$7=D2,$B$2:$B$7,""))
Dynamic array formulas.

 

[Jaybeerex]

Well that is certainly a far simpler way of sorting it that I've been trying to!

That's perfect for building the mailing list and if i need other line elements lost when it users the helper column and unaligns with the original data i can just vlookup back from the email address for contact name etc. thank you!

the only thing i seem to be losing is the leading zero when it does the join into a new column, i need this for telephone number formatting to look correct, i've tried text formatting etc but i keep losing the leading 0 when it moves, any thoughts?

 

[JamesCanale]

Use the 'text' function. I noticed that one of your sample data is 10 digits instead of 11. This is set up for 11 digits. If you have variable digit-length, I'm wondering how you format your second column - but otherwise you might need to store the numbers as text if they are variable length with a leading single-quote. However, since they are phone numbers, I suspect that that shorter number was an error of some kind.

Mr Excel Playground 3.xlsm
	A	B	C	D	E
1	Email	Service		email	service
2	a@b.com	00000000011		a@b.com	00000000011, 00000000022
3	a@b.com	00000000022		c@b.com	00000000033
4	c@b.com	00000000033		d@e.com	00000000044, 00000000055, 00000000066
5	d@e.com	00000000044
6	d@e.com	00000000055
7	d@e.com	00000000066
Sheet10

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A2:A7)
E2:E4	E2	=TEXTJOIN(", ",TRUE,IF($A$2:$A$7=D2,TEXT($B$2:$B$7,"00000000000"),""))
Dynamic array formulas.