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!

 

[AtariBaby]

Can you show a manual example of the expected output as well?

I'm open to whatever but here would be an example. John Smith and Peggy Smith were reply-all'd and ideally, they would both contain the subject line. OR the subject line could be blank on subsequent subject line cells until the next email, which was to John Johnson only. Or if it was the old excel trick of creating a number reference to each row (like the email to john and peggy smith would both have a column with the number 1, based on subject line, and the john johnson had the number 2 because it's a new email and new subject line, and the next email subject would be 3.

Name	Email	Subject
John Smith	john@smith.com	Yolo
Peggy Smith	peggy@smith.com	Yolo
John Johnson	jjsmooth@gmail.com	meeting postponed

 

[AtariBaby]

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

i'm sorry, can you dumb this down for me? I feel like it happened automatically after I put the formula in F2 cell

 

[AtariBaby]

sorry to go off topic, but is there no edit button in this forum?

 

[Cubist]

i'm sorry, can you dumb this down for me? I feel like it happened automatically after I put the formula in F2 cell

It looks like there's stuff below F2. The formula only needs to be inserted in F2, clear out everything below and to the right. You can edit within 10 minutes (bottom left).

 

[AtariBaby]

oh my god you're a genius. I think it worked!!!

 

[AtariBaby]

Hi, something strange. The formula works but only down to about the 24th row, which creates beautifully broken out rows of one email on each line for about 44 rows, but then stops. There are thousands of entries. If I try to create a table that goes down to the last row, i get the "SPILL!" error. I am not manually dragging anything down. I have also completely deleted the column with the formula and wind up with the same result

 

[Cubist]

Did you adjust the range accordingly?
If there's nothing below, and you're getting the message it indicates you're hitting Excel has a row limit, if there are too many entries,. You might have to break it into different sheets.

 

[AtariBaby]

More observations:
I think this might work down to the first time there are more than two names / email addresses in cells. I think the next one contains three and that's where it stops.

It seems to spit out the desired result in duplicate. So if it's John Smith; Peggy Smith, I get a column with

John Smith
Peggy Smith
John Smith
Peggy Smith

before going on to the next email subject.

 

[AtariBaby]

Did you adjust the range accordingly?
If there's nothing below, and you're getting the message it indicates you're hitting Excel has a row limit, if there are too many entries,. You might have to break it into different sheets.

Thank you but see my previous response. i think i've tried to adjust range. what's the surest way to do it?

i suspect it's more than 2 names/emails that is where it stops. if i had to break it into separate sheets at 24 rows per sheets that would mean 133 sheets.

 

[Cubist]

I suspect you're not grabbing the full range. Show what you entered in for your formula.
Try this version:

=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,3)),"|","%"),
u,UNIQUE(FILTER(ts,((CHOOSECOLS(ts,1)<>"")*(CHOOSECOLS(ts,2)<>"")))),u)