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]

I'll try the new one now. Here's what I was last using:

=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)<>"")))))

 

[Cubist]

You're not adjusting the cell reference to your actual data. You only provided 22 rows of data so my references only go to 22. See the red.

=LET(names,TEXTSPLIT(TEXTJOIN("|",,NAME RANGE),";","|",,,""),
name_ct,MAX(BYROW(names,LAMBDA(r,SUM(--(r<>""))))),
email,TEXTSPLIT(TEXTJOIN("|",,EMAIL RANGE),";","|",,,""),
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(SUBJECT RANGE,r))),
ts,TEXTSPLIT(TEXTJOIN("%",,TOCOL(arr)),"|","%"),
UNIQUE(FILTER(ts,((CHOOSECOLS(ts,1)<>"")*(CHOOSECOLS(ts,2)<>"")))))

 

[AtariBaby]

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)

Same issues. Seems to duplicate results. Seemingly stopped by the row with more than two names/emails in a cell.

I tried something. I put that more-than-two row on the top. I got an interesting result.

You're not adjusting the cell reference to your actual data. You only provided 22 rows of data so my references only go to 22. See the red.

=LET(names,TEXTSPLIT(TEXTJOIN("|",,NAME RANGE),";","|",,,""),
name_ct,MAX(BYROW(names,LAMBDA(r,SUM(--(r<>""))))),
email,TEXTSPLIT(TEXTJOIN("|",,EMAIL RANGE),";","|",,,""),
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(SUBJECT RANGE,r))),
ts,TEXTSPLIT(TEXTJOIN("%",,TOCOL(arr)),"|","%"),
UNIQUE(FILTER(ts,((CHOOSECOLS(ts,1)<>"")*(CHOOSECOLS(ts,2)<>"")))))

oh i'm so sorry. i didn't realize. what is the proper syntax for each range? "C2-C2000" for example?

 

[AtariBaby]

Same issues. Seems to duplicate results. Seemingly stopped by the row with more than two names/emails in a cell.

I tried something. I put that more-than-two row on the top. I got an interesting result.

oh i'm so sorry. i didn't realize. what is the proper syntax for each range? "C2-C2000" for example?

oh colon looks like. i'll try it

 

[AtariBaby]

So I put in this:

=LET(names,TEXTSPLIT(TEXTJOIN("|",,A2:A500),";","|",,,""),
name_ct,MAX(BYROW(names,LAMBDA(r,SUM(--(r<>""))))),
email,TEXTSPLIT(TEXTJOIN("|",,B2:B500),";","|",,,""),
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:C500,r))),
ts,TEXTSPLIT(TEXTJOIN("%",,TOCOL(arr)),"|","%"),
UNIQUE(FILTER(ts,((CHOOSECOLS(ts,1)<>"")*(CHOOSECOLS(ts,2)<>"")))))

and the error was TEXTJOIN: Text too long

I remembered you said might have to break into sheets. And then I tried to shorten by pasting this:

=LET(names,TEXTSPLIT(TEXTJOIN("|",,A2:A500),";","|",,,""),
name_ct,MAX(BYROW(names,LAMBDA(r,SUM(--(r<>""))))),
email,TEXTSPLIT(TEXTJOIN("|",,B2:B500),";","|",,,""),
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:C500,r))),
ts,TEXTSPLIT(TEXTJOIN("%",,TOCOL(arr)),"|","%"),
UNIQUE(FILTER(ts,((CHOOSECOLS(ts,1)<>"")*(CHOOSECOLS(ts,2)<>"")))))

I tried it without narrowing down to 500 rows and the result was "0" on the firs cell. I then deleted all but the first 500 rows and got the same single 0 as a result.

 

[Cubist]

Can't see what you're doing so can't comment. If you click inside the formula bar, it should highlight the ranges it's referencing, make sure those are correct.

 

[AtariBaby]

Okay i tried working out of a new sheet and that didn’t go well at all. But working with the previous sheet you and I started with, I see now that the issue is the number of rows. 45 max. That will mean breaking out in 75 sheets, or maybe i go down the existing table (1:45) and adjust the range (46:91) and so on. Unless you know of a better way?

Also, if I was to eliminate the cells with single names/emails (no semicolon) that would reduce the number of cells I have to process this way. I can probably look that up myself unless you have that at your fingertips!

Thank you, this is more progress than I've been able to make, by far. I would be happy to buy you some beer or pizza or coffees at the end of this.

 

[Cubist]

The limitation isn't the number of rows but the number of characters altogether in the range. The TEXTJOIN function has a limitation of 32,000 characters per formula.
Note that the formula spills so it is not recommended to do it underneath, but potentially left to right, to avoid the #SPILL! error.
You have an awful lot of data to process, and this is all I can offer.

Glad to help, but save your money for a rainy day. Cheers.

 

[AtariBaby]

The limitation isn't the number of rows but the number of characters altogether in the range. The TEXTJOIN function has a limitation of 32,000 characters per formula.
Note that the formula spills so it is not recommended to do it underneath, but potentially left to right, to avoid the #SPILL! error.
You have an awful lot of data to process, and this is all I can offer.

Glad to help, but save your money for a rainy day. Cheers.

thanks again

 

[AtariBaby]

Just want to add that in order to speed things up and eliminate unnecessary work, I used this on a column, then sorted by the number of semicolons, allowing me to skip running the formula on rows that don't need it, and to give me a better sense of how many rows I can process before getting a "CALC!" or similar error

= LEN(A2) - LEN(SUBSTITUTE(A2, ";", "")) + 1