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!

 

[Alex Blakenburg]

I know @Cubist has put quite a bit of time into this and is quite capable of providing VBA so perhaps that was ruled out at some point but just in case, here is a VBA option.
It assumes that your data sheet is the active sheet when you run it and it will create a new sheet with the output.

Sub ReorientEmailAddresses()

    Dim shtSrc As Worksheet, shtOut As Worksheet
    Dim rowLast As Long
    Dim rngSrc As Range
    Dim arrSrc As Variant, arrName As Variant, arrEmail As Variant
    Dim arrOut() As Variant, hdgOut As Variant
    Dim i As Long, j As Long, iOut As Long
    Dim cntName As Long, cntEmail As Long, cntMax As Long
    Dim idxSubject As Long, Subject As String
    Dim idxDetail As Long, sName As String, sEmail As String
    
    Set shtSrc = ActiveSheet                ' <--- Ideally use the sheet name Worksheets("Sheet_Name")
    With shtSrc
        rowLast = .Range("A" & Rows.Count).End(xlUp).Row
        Set rngSrc = .Range(.Cells(2, "A"), .Cells(rowLast, 3))
        arrSrc = rngSrc.Value
    End With
    
    hdgOut = Array("No", "Subject", "Name", "Email", "Item No")

    ReDim arrOut(1 To 100000, 1 To UBound(arrSrc, 2) + 2)
    For i = 1 To UBound(arrSrc)
        idxSubject = i
        Subject = arrSrc(i, 3)
    
        arrName = Split(arrSrc(i, 1), ";")
        arrEmail = Split(arrSrc(i, 2), ";")
        cntName = UBound(arrName)
        cntEmail = UBound(arrEmail)
        cntMax = cntName
        If cntMax < cntEmail Then cntMax = cntEmail
        
        For j = 0 To cntMax
            sName = ""
            sEmail = ""
            If j <= cntName Then
                sName = Trim(arrName(j))
            End If
            
            If j <= cntEmail Then
                sEmail = Trim(arrEmail(j))
            End If
            
            iOut = iOut + 1
            arrOut(iOut, 1) = idxSubject
            arrOut(iOut, 2) = Subject
            arrOut(iOut, 3) = sName
            arrOut(iOut, 4) = sEmail
            arrOut(iOut, 5) = j + 1
        Next j
    Next i

    Set shtOut = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    With shtOut
        With .Range("A1")
            ' Headings
            .Resize(, UBound(hdgOut) + 1).Value = hdgOut
            .Resize(, UBound(hdgOut) + 1).Font.Bold = True
            ' Data
            .Offset(1).Resize(iOut, UBound(arrOut, 2)) = arrOut
            ' Format
            .Resize(, UBound(hdgOut) + 1).EntireColumn.AutoFit
        End With
    
    End With
End Sub

 

[Alex Blakenburg]

Does make any difference if you add a zero in this line ? ie change from 100k to 1m
ReDim arrOut(1 To 100000, 1 To UBound(arrSrc, 2) + 2)

What happen when it "breaks" ?
Do you get an error ?
Do some lines work and some not ?
Is the delimiter definitely still ";" ?
Are there any merged cells ?
Does Cubist formula produce a result on the same data that breaks the code ?

 

[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.