Breaking out names and email addresses that are many reply alls in one column, without losing reference point

[AtariBaby]

I have a spreadsheet that looks like this. I exported it from Outlook.

Col A Names | Col B email | Col C Subject

The idea was to see what contacts to keep and what to delete, with the name and subject line helping the human who makes that decision.

The problem is the list contains many cells with multiple names and email addresses. I have wracked my brains and asked a lot of excel people:

How can I break those multiple items, separated by semicolons into one per cell, but still have a reference point to the subject line? And how do I also do the Names cell so it’s synchronous with the broken out email cells?

Whether Excel formulas or 3rd party app or tool, I just really want to solve this problem I’m stuck on.

Thanks for being here and for reading this!

 

[Cubist]

Can you provide some examples of what the data look like? You can use XL2BB to post the sample to the forum.

 

[AtariBaby]

Maybe I can do a quick mockup. the list contains personal information so maybe I can just make up a few rows

 

[AtariBaby]

Hi, would you permit me two issues?
Firstly, i hope you don’t mind, I’m on an iPad so I used a website for creating the bbcode. Secondly, i found formulas to obfuscate confidential info, but the names column comes out as email addresses. Would you do me a favor and pretend those name column cells with multiples separated by semicolons are actually first name last name, not email?

Name	Email	Subject
son*****cengineering.com	son*****cengineering.com	5:30 tonight at A16? Ken
jma*****rm4inc.com;jmarx1@speakeasy.net	jma*****rm4inc.com; jmar*****eakeasy.net	7 feet tall, 4 feet wide, 2.5 feet deep, holds 300 bottles, brand is Le Cache
mark.radclif*****apiper.com;railski.baldy@gmail.com	mark.radclif*****apiper.com; railski.bal*****ail.com	A book is coming your way
nknapi*****rthlink.net	nknapi*****rthlink.net	A book cover
b*****fwar.com	b*****fwar.com	A change
csalinasn*****ail.com;Jkcats210@gmail.com	csalinasn*****ail.com; Jkcats2*****ail.com	A favor
daniel.juli*****le.edu	daniel.juli*****le.edu	A good articl
natew6*****ail.com;nwilcox1023@gmail.com	natew6*****ail.com; nwilcox10*****ail.com	A message for Kurt
Groslyn.M.Burt*****c.eop.gov	Groslyn.M.Burt*****c.eop.gov	A neighbor with a question
bertcast*****l.com	bertcast*****l.com	A Problem
customerhe*****onomist.com	customerhe*****onomist.com	A promising company
JGoldste*****b.com;MGallagher@svb.com	JGoldste*****b.com; MGallagh*****b.com	A question
john.kavanau*****norcal.com	john.kavanau*****norcal.com	A request
david.ly*****morgan.com	david.ly*****morgan.com	A session with your students
adkinsj*****u.edu;gatsbykjg@gmail.com	adkinsj*****u.edu; gatsbyk*****ail.com	A small favor
mselfrid*****rstrepublic.com	mselfrid*****rstrepublic.com	a submission
op*****times.com	op*****times.com	A virtual gala
alfred.c*****meicapital.com;alfred@alfredchu.com	alfred.c*****meicapital.com; alfr*****fredchu.com	A virtual gala
nicholaslfull*****ail.com	nicholaslfull*****ail.com	Additions to the B&F Committee
fffn*****l.com;jxu@asianart.org;nperrone@asianart.org	fffn*****l.com; j*****ianart.org; nperro*****ianart.org	Advice
Liu*****db.com	Liu*****db.com	Aileen, is this still a valid email address

 

[Cubist]

This is ok. I'm re-reading your original post and comparing it to the data you provided, I'm not clear on what the desired the result is. Can you show a manual example of the expected output as well?

 

[Cubist]

This is the condensed version.

=LET(names,TEXTSPLIT(TEXTJOIN("|",,A2:A22),";","|",,,""),
name_ct,BYROW(names,LAMBDA(r,SUM(--(r<>"")))),
email,TEXTSPLIT(TEXTJOIN("|",,B2:B22),";","|",,,""),
email_ct,BYROW(email,LAMBDA(r,SUM(--(r<>"")))),
arr,MAKEARRAY(ROWS(names),MAX(name_ct*email_ct),LAMBDA(r,c,INDEX(names,r,ROUNDUP(c/3,0))&"|"&INDEX(email,r,MOD(c-1,3)+1)&"|"&INDEX(C2:C22,r))),
ts,TEXTSPLIT(TEXTJOIN("%",,TOCOL(arr)),"|","%"),
FILTER(ts,((CHOOSECOLS(ts,1)<>"")*(CHOOSECOLS(ts,2)<>""))))

 

[Cubist]

Some adjustments

=LET(names,TEXTSPLIT(TEXTJOIN("|",,A2:A22),";","|",,,""),
name_ct,MAX(BYROW(names,LAMBDA(r,SUM(--(r<>""))))),
email,TEXTSPLIT(TEXTJOIN("|",,B2:B22),";","|",,,""),
email_ct,MAX(BYROW(email,LAMBDA(r,SUM(--(r<>""))))),
arr,MAKEARRAY(ROWS(names),name_ct*email_ct,
LAMBDA(r,c,INDEX(names,r,ROUNDUP(c/name_ct,0))&"|"&INDEX(email,r,MOD(c-1,email_ct)+1)&"|"&INDEX(C2:C22,r))),
ts,TEXTSPLIT(TEXTJOIN("%",,TOCOL(arr)),"|","%"),
UNIQUE(FILTER(ts,((CHOOSECOLS(ts,1)<>"")*(CHOOSECOLS(ts,2)<>"")))))

 

[AtariBaby]

This is ok. I'm re-reading your original post and comparing it to the data you provided, I'm not clear on what the desired the result is. Can you show a manual example of the expected output as well?

Sorry if I’m being vague! Maybe I should try what you’ve made first, then I can show how I’d like that.

 

[AtariBaby]

hi this is what i got when i tried your formula. i get this a lot.

 

[Cubist]

It returns an array, don't need to need drag it down.