Separating multiple email addresses and names in Excel

[russrokicki]

Hi,

I need help with an Excel sheet. I have a list of users names and email addresses along with other data columns. Some of the Name and email cells contain multiple names and corresponing email address. I want to have these moved to their own row with all the other row/column data duplicated.

The names are unfortunately formatted like this: Anderson, Ashley C., Beard, James, Nextor, Ian

The email addresses are separated by a comma.

Is there a way to give each name and email address their own row and copy all the data from the original columns?

so original would have column a Name column B email column C Request Title D. date E. Status
the new rows would have all the same information but only one name per.

There are also many rows with only one name that I would not want to be affected.

Thanks in Advance!

Russell

 

[yky]

Re: Need help separating multiple email addresses and names in Excel

Not quite sure how your data look like. If there are multiple names and emails in cells, how do name correspond to email?

Anyway, if you want to do it manually, you may want to look into Data/Text to Columns and set delimiter to comma. Copy name and email cells to a new sheet and play with it. You then copy across columns and paste/transpose to a single column. Depending on the data, it may take a long time. VBA may be the way to go.

 

[alansidman]

Re: Need help separating multiple email addresses and names in Excel

Really need to see several rows of data shown in a manner that we can distinguish what you have so that we can provide a workable solution. What you have provided is not helpful.

 

[Peter_SSs]

Re: Need help separating multiple email addresses and names in Excel

I have assumed that ..
a) All names consist of two parts separated by a comma (& possibly a space). If some names contained a comma and some didn't (or contained more than one comma) then there would be no way to identify where one name ends and the next one starts
b) The number of email addresses in column B always matches the number of names in column A.

Given that, you could try this

Sub Rearrange()
  Dim a As Variant, b As Variant, NameBits As Variant, EmailBits As Variant
  Dim c As Long, i As Long, j As Long, k As Long, uba2 As Long
  
  a = Range("A2:E" & Range("A" & Rows.Count).End(xlUp).Row).Value
  uba2 = UBound(a, 2)
  ReDim b(1 To Rows.Count, 1 To uba2)
  For i = 1 To UBound(a)
    NameBits = Split(Replace(a(i, 1), ", ", ","), ",")
    EmailBits = Split(Replace(a(i, 2), ", ", ","), ",")
    For j = 0 To UBound(EmailBits)
      k = k + 1
      b(k, 1) = NameBits(2 * j) & ", " & NameBits(2 * j + 1)
      b(k, 2) = EmailBits(j)
      For c = 3 To uba2
        b(k, c) = a(i, c)
      Next c
    Next j
  Next i
  With Range("G2").Resize(k, uba2)
    .Value = b
    .Rows(0).Value = Range("A1").Resize(, uba2).Value
    .EntireColumn.AutoFit
  End With
End Sub

My sample data in columns A:E, code results in G:K

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Email	Request	Date	Status		Name	Email	Request	Date	Status
2	Anderson, Ashley C., Beard, James, Nextor, Ian	abc@def.com, ttt@abc.net, hhh_jjj123@ggg.com.uk	Req 1	4/10/2019	Pending		Anderson, Ashley C.	abc@def.com	Re1 1	4/10/2019	Pending
3	Doe, John	Johndoe@myplace.com.jp	Req 2	5/10/2019	Complete		Beard, James	ttt@abc.net	Re1 1	4/10/2019	Pending
4	Derf, Fred, Derf, Frederika	DerfFred@abc.def, DerfFrederika@abc.def	Req 3	6/10/2019	Not Started		Nextor, Ian	hhh_jjj123@ggg.com.uk	Re1 1	4/10/2019	Pending
5							Doe, John	Johndoe@myplace.com.jp	Re1 2	5/10/2019	Complete
6							Derf, Fred	DerfFred@abc.def	Re1 3	6/10/2019	Not Started
7							Derf, Frederika	DerfFrederika@abc.def	Re1 3	6/10/2019	Not Started
Sheet1

 

[russrokicki]

Re: Need help separating multiple email addresses and names in Excel

Thank you! This seems to do exactly what I wanted! I have to review all the new data but on first glance this is exactly what I wanted!

I have assumed that ..
a) All names consist of two parts separated by a comma (& possibly a space). If some names contained a comma and some didn't (or contained more than one comma) then there would be no way to identify where one name ends and the next one starts
b) The number of email addresses in column B always matches the number of names in column A.

Given that, you could try this

Sub Rearrange()
  Dim a As Variant, b As Variant, NameBits As Variant, EmailBits As Variant
  Dim c As Long, i As Long, j As Long, k As Long, uba2 As Long
  
  a = Range("A2:E" & Range("A" & Rows.Count).End(xlUp).Row).Value
  uba2 = UBound(a, 2)
  ReDim b(1 To Rows.Count, 1 To uba2)
  For i = 1 To UBound(a)
    NameBits = Split(Replace(a(i, 1), ", ", ","), ",")
    EmailBits = Split(Replace(a(i, 2), ", ", ","), ",")
    For j = 0 To UBound(EmailBits)
      k = k + 1
      b(k, 1) = NameBits(2 * j) & ", " & NameBits(2 * j + 1)
      b(k, 2) = EmailBits(j)
      For c = 3 To uba2
        b(k, c) = a(i, c)
      Next c
    Next j
  Next i
  With Range("G2").Resize(k, uba2)
    .Value = b
    .Rows(0).Value = Range("A1").Resize(, uba2).Value
    .EntireColumn.AutoFit
  End With
End Sub

My sample data in columns A:E, code results in G:K

Sheet1

	A	B	C	D	E	F	G	H	I	J	K
1	Name	Email	Request	Date	Status		Name	Email	Request	Date	Status
2	Anderson, Ashley C., Beard, James, Nextor, Ian	abc@def.com, ttt@abc.net, hhh_jjj123@ggg.com.uk	Req 1	4/10/2019	Pending		Anderson, Ashley C.	abc@def.com	Re1 1	4/10/2019	Pending
3	Doe, John	Johndoe@myplace.com.jp	Req 2	5/10/2019	Complete		Beard, James	ttt@abc.net	Re1 1	4/10/2019	Pending
4	Derf, Fred, Derf, Frederika	DerfFred@abc.def, DerfFrederika@abc.def	Req 3	6/10/2019	Not Started		Nextor, Ian	hhh_jjj123@ggg.com.uk	Re1 1	4/10/2019	Pending
5							Doe, John	Johndoe@myplace.com.jp	Re1 2	5/10/2019	Complete
6							Derf, Fred	DerfFred@abc.def	Re1 3	6/10/2019	Not Started
7							Derf, Frederika	DerfFrederika@abc.def	Re1 3	6/10/2019	Not Started

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:307px;"><col style="width:332px;"><col style="width:67px;"><col style="width:76px;"><col style="width:85px;"><col style="width:16px;"><col style="width:142px;"><col style="width:188px;"><col style="width:74px;"><col style="width:84px;"><col style="width:99px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

Re: Need help separating multiple email addresses and names in Excel

Thank you! This seems to do exactly what I wanted! I have to review all the new data but on first glance this is exactly what I wanted!

You're welcome. If any tweaks are required, post back with details.

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only or just enough so readers know who you are addressing.

 

[russrokicki]

Re: Need help separating multiple email addresses and names in Excel

You're welcome. If any tweaks are required, post back with details.

Thanks again! I will be more careful with the quoting. I was hoping you could help tweak this a little for two purposes.

The first is if I have more than 5 columns of data with different information that i would want to retain. One sheet goes to Column L

The second tweak is I have another sheet that has duplicate emails but does not have multiple names or in some cases no name at all but i still want to separate the email address. The columns are similar otherwise and go to Column G (Name, Email, Request, Date, Status, Search Content)

Thanks again!

 

[Peter_SSs]

Re: Need help separating multiple email addresses and names in Excel

The first is if I have more than 5 columns of data with different information that i would want to retain. One sheet goes to Column L

This should cope with any number of columns provided that row 1 can be safely used to determine what the last column is. The only changed lines are highlighted

Sub Rearrange_v2()
  Dim a As Variant, b As Variant, NameBits As Variant, EmailBits As Variant
  Dim c As Long, i As Long, j As Long, k As Long, uba2 As Long
  
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, Cells(1, Columns.Count).End(xlToLeft).Column).Value
  uba2 = UBound(a, 2)
  ReDim b(1 To Rows.Count, 1 To uba2)
  For i = 1 To UBound(a)
    NameBits = Split(Replace(a(i, 1), ", ", ","), ",")
    EmailBits = Split(Replace(a(i, 2), ", ", ","), ",")
    For j = 0 To UBound(EmailBits)
      k = k + 1
      b(k, 1) = NameBits(2 * j) & ", " & NameBits(2 * j + 1)
      b(k, 2) = EmailBits(j)
      For c = 3 To uba2
        b(k, c) = a(i, c)
      Next c
    Next j
  Next i
  With Cells(2, uba2 + 2).Resize(k, uba2)
    .Value = b
    .Rows(0).Value = Range("A1").Resize(, uba2).Value
    .EntireColumn.AutoFit
  End With
End Sub

The second tweak is I have another sheet that has duplicate emails but does not have multiple names or in some cases no name at all but i still want to separate the email address.

If you don't have a one-to-one relationship between names and email addresses, I don't know what logic you would use to link them. With the example below, how would we know whether 2 emails belonged to John Doe and 1 to Fred Derf or vice-versa or even if all 3 emails belonged John and none to Fred (or vice-versa)?

Excel 2016
	A	B
11	Doe, John, Derf, Fred	email1,email2,email3
Sheet3

If you cannot be sure of such a 1-to-1 relationship, then even if there are say 2 names and 2 email addresses, it might be that both email addresses belong to the first person and none belong to the second person.