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

 

[Cubist]

@Alex Blakenburg VBA is better for the OP as s/he has a lot of data. I'm not on my Windows machine so can't offer VBA. One thing to note the number of output rows of each entry is determined by the number of emails times the number of names. The OP has 2,000 entries so the output rows grow pretty quickly. I doubt 1 sheet will cut it. Another thing to consider is the file size. We didn't discuss if there's anything else in there already. The OP can provide more info regarding this matter. I don't have anything else to add here so I'll let you take over.

 

[AtariBaby]

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.

Hi, @Alex Blakenburg I loved this! The only problem is, it gave me a list of numbers and the subject line, without the name and email. any idea what i might have done wrong?

 

[Alex Blakenburg]

Can you give me a picture showing the row & column references, the headings and at least 1 row of data.
Also how many rows in your data set.

If you want to look from your end first how is your data different to this:

20240419 VBA Split 2 Columns AtariBaby v02 large data.xlsm
	A	B	C
1	Name	Email	Subject
2	John Brown; Tom Jones	Jbrown@gm.com; tomjones@ms.com	5:30 tonight at A16? Ken
3	Mary Jane Smith; Peter Green; Sam A Violet	mary_smith@xx.com; pgreen@ms.com; av@gm.com	7 feet tall, 4 feet wide, 2.5 feet deep, holds 300 bottles, brand is Le Cache
Raw Data (2)

 

[Cubist]

@Alex Blakenburg This might help. The names need to be cross-joined with the email. See the desired output.

Book1
	A	B	C	D	E	F	G
1	Name	Email	Subject		Name	Email	Subject
2	John Brown; Tom Jones	Jbrown@gm.com; tomjones@ms.com	5:30 tonight at A16? Ken		John Brown	Jbrown@gm.com	5:30 tonight at A16? Ken
3	Mary Jane Smith; Peter Green; Sam A Violet	mary_smith@xx.com; pgreen@ms.com; av@gm.com	7 feet tall, 4 feet wide, 2.5 feet deep, holds 300 bottles, brand is Le Cache		John Brown	tomjones@ms.com	5:30 tonight at A16? Ken
4					Tom Jones	Jbrown@gm.com	5:30 tonight at A16? Ken
5					Tom Jones	tomjones@ms.com	5:30 tonight at A16? Ken
6					Mary Jane Smith	mary_smith@xx.com	7 feet tall, 4 feet wide, 2.5 feet deep, holds 300 bottles, brand is Le Cache
7					Mary Jane Smith	pgreen@ms.com	7 feet tall, 4 feet wide, 2.5 feet deep, holds 300 bottles, brand is Le Cache
8					Mary Jane Smith	av@gm.com	7 feet tall, 4 feet wide, 2.5 feet deep, holds 300 bottles, brand is Le Cache
9					Peter Green	mary_smith@xx.com	7 feet tall, 4 feet wide, 2.5 feet deep, holds 300 bottles, brand is Le Cache
10					Peter Green	pgreen@ms.com	7 feet tall, 4 feet wide, 2.5 feet deep, holds 300 bottles, brand is Le Cache
11					Peter Green	av@gm.com	7 feet tall, 4 feet wide, 2.5 feet deep, holds 300 bottles, brand is Le Cache
12					Sam A Violet	mary_smith@xx.com	7 feet tall, 4 feet wide, 2.5 feet deep, holds 300 bottles, brand is Le Cache
13					Sam A Violet	pgreen@ms.com	7 feet tall, 4 feet wide, 2.5 feet deep, holds 300 bottles, brand is Le Cache
14					Sam A Violet	av@gm.com	7 feet tall, 4 feet wide, 2.5 feet deep, holds 300 bottles, brand is Le Cache
Sheet7

Cell Formulas
Range	Formula
E2:G14	E2	=LET(names,TEXTSPLIT(TEXTJOIN("|",,A2:A3),";","|",,,""), name_ct,MAX(BYROW(names,LAMBDA(r,SUM(--(r<>""))))), email,TEXTSPLIT(TEXTJOIN("|",,B2:B3),";","|",,,""), 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:C3,r))), ts,TEXTSPLIT(TEXTJOIN("%",,TOCOL(arr,3)),"|","%"), u,UNIQUE(FILTER(ts,((CHOOSECOLS(ts,1)<>"")*(CHOOSECOLS(ts,2)<>"")))),u)
Dynamic array formulas.

 

[Alex Blakenburg]

Thanks @Cubist. Based on the word "synchronous" in post #1 I am not sure a cross join it what is required.
What I did do was to contain a mismatch in the number of names & email address on a single line to that subject so that it didn't throw everything below it out of alignment.

And how do I also do the Names cell so it’s synchronous with the broken out email cells?

@AtariBaby, please confirm that you are expecting on a single row, that name 1 is on the same line as email 1, name 2 the same line email 2 etc.

My output for the same data as in Post #35 is:

20240419 VBA Split 2 Columns AtariBaby v02 large data.xlsm
	A	B	C	D	E
1	No	Subject	Name	Email	Item No
2	1	5:30 tonight at A16? Ken	John Brown	Jbrown@gm.com	1
3	1	5:30 tonight at A16? Ken	Tom Jones	tomjones@ms.com	2
4	2	7 feet tall, 4 feet wide, 2.5 feet deep, holds 300 bottles, brand is Le Cache	Mary Jane Smith	mary_smith@xx.com	1
5	2	7 feet tall, 4 feet wide, 2.5 feet deep, holds 300 bottles, brand is Le Cache	Peter Green	pgreen@ms.com	2
6	2	7 feet tall, 4 feet wide, 2.5 feet deep, holds 300 bottles, brand is Le Cache	Sam A Violet	av@gm.com	3
Sheet2

 

[AtariBaby]

Hi, @Cubist I am not sure why my outcome was different but I’ll endeavor to answer your questions in a moment. Also forgive me if I choose poor wording in my OP.

Ideally, I’d like the output to be
Name1, Email1, Subject1
Name2, Email2, Subject1
Name3, Email3, Subject1
Name1,Email1, Subject2
Name2, Email2, Subject2

Does that make sense?

 

[AtariBaby]

^in rows

 

[Alex Blakenburg]

Can you respond to my post #34 ?

 

[AtariBaby]

Hi @Alex Blakenburg , Your script is working when I do a small set like this! (i muddled the names and email addresses for this example). But if I do the entire rows, I get an output of
column1
sequential number
column2
subject line

my data looks like this:

Name	Email	Subject
mick@ldldlcoin.com;mick asd	mick@ldldlcoin.com;rangeoflight@gldldll.com	mick, I’m falling asleep. Can we change our call to 7:00 am (10:00 pm your time) please. I’m going to bed.
mick@ldldlcoin.com;Bruce blerp	mick@ldldlcoin.com;Bblerp@svb.com	mick, please contact Bruce blerp (see above) regarding block chain. He is expecting you.
mick@ldldlcoin.com;wave Jones	mick@ldldlcoin.com;djones@spd-nowhere.com	mick, please wave Jones (eldldll address above). He is expecting you. Ken
ajm@notinc.com	ajm@notinc.com	mick, thanks so much for sending me another copy of the book. I will put it to good use. Ken
Dar, Jul;Jai Rams	ddlius@gmail.com;jaitdms@icloud.com	All set for Monday?

Ideally the rows would be name, email, subject but i will gladly accept the columns in any order!

I have no idea why it breaks when I do the entire thing.

I tried doing 1000 and it broke as described. I deleted everything after row 300 and ran it again and got the same undesired result, a column of sequential numbers and a subject line