VBA Split semicolon and comma separated values into new rows

[Adalinda]

Hi there,

These are some screenshots of what I'm trying to do:

I am trying to split the Alias names into in new rows. There can be 1+ alias names which are separated by semicolon (. The name format is LAST, FIRST MIDDLE; LAST, FIRST MIDDLE..... Then I need to duplicate the DOB and Additional Columns in each new row. It would be great it I could do all of this in the sheet I'm working in. Right now I am manually insert rows rows and copy/pasting the info and delete from the alias column. I have to do this for about 200 names at a time.

I tried to use the thread info from the post VBA Split comma separated values in new rows and return the original description (VBA Split comma separated values in new rows and return the original description) but I could not get it to work. I am new at macros and would greatly appreciate any help! Thanks

 

[lrobbo314]

It seems like the output is inconsistent as to what happens when the 'Alias' column is split.

Here is my attempt in PQ. Let me know if this is what you are looking for.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tmp = Table.AddColumn(Source, "tmp", each if [Alias] is null then [Alias] else [Last] & " ; " & [Alias]),
    Split = Table.TransformColumns(tmp,{{"tmp", each try Text.Split(_, " ; ") otherwise _}}),
    Expand = Table.ExpandListColumn(Split, "tmp"),
    SplitList = Table.SplitColumn(Expand, "tmp", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"tmp.1", "tmp.2"}),
    RC = Table.RemoveColumns(SplitList,{"Last", "Alias"}),
    Rename = Table.RenameColumns(RC,{{"tmp.1", "Last"}, {"tmp.2", "Alias"}}),
    Reorder = Table.ReorderColumns(Rename,{"First", "Middle", "Last", "Alias", "DOB", "Additional"})
in
    Reorder

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	First	Middle	Last	Alias	DOB	Additional		First	Middle	Last	Alias	DOB	Additional
2	Jennifer		Davis	Miller, Jennifer ; Wilcox, Jennifer M	9/13/2008	WASHINGTON		Jennifer		Davis		9/13/2008	WASHINGTON
3	Swift		Tony		4/11/2015	BENTON		Jennifer		Miller	Jennifer	9/13/2008	WASHINGTON
4	Carmen	Shera	Planter		10/30/1982	PHILLIPS		Jennifer		Wilcox	Jennifer M	9/13/2008	WASHINGTON
5	Carmen		Planter		10/30/1982	CONWAY		Swift				4/11/2015	BENTON
6	Linda		Gulley	Mccolly, Linda ; Williams, Lenny ; Robertson, Lenny	6/25/1991	BENTON		Carmen	Shera			10/30/1982	PHILLIPS
7	Jeremiah		Munroe	Monroe, Jeremiah ; Baldrew, Jeremiah	5/14/1986	LONOKE		Carmen				10/30/1982	CONWAY
8	Jeremiah		Munroe	Monroe, Jeremiah ; Baldrew, Jeremiah	5/14/1986	BENTON		Linda		Gulley		6/25/1991	BENTON
9	Robert		Doe		3/23/2008	LONOKE		Linda		Mccolly	Linda	6/25/1991	BENTON
10	John	Jacob	Jingle		8/22/1996	CONWAY		Linda		Williams	Lenny	6/25/1991	BENTON
11	Kevin	Anthony	Valdez		5/19/2002	LONOKE		Linda		Robertson	Lenny	6/25/1991	BENTON
12	Kevin		Mockingbird		5/19/2002	PHILLIPS		Jeremiah		Munroe		5/14/1986	LONOKE
13								Jeremiah		Monroe	Jeremiah	5/14/1986	LONOKE
14								Jeremiah		Baldrew	Jeremiah	5/14/1986	LONOKE
15								Jeremiah		Munroe		5/14/1986	BENTON
16								Jeremiah		Monroe	Jeremiah	5/14/1986	BENTON
17								Jeremiah		Baldrew	Jeremiah	5/14/1986	BENTON
18								Robert				3/23/2008	LONOKE
19								John	Jacob			8/22/1996	CONWAY
20								Kevin	Anthony			5/19/2002	LONOKE
21								Kevin				5/19/2002	PHILLIPS
Sheet1

 

[Adalinda]

I am very new at this. I will have to see if I have Power Query and how to load this into it

 

[Akuini]

@Adalinda
I think I can create a macro to handle this task, but I got a bit confused by the alias part.
Is the second image in post #1 actually the expected result? If not, could you post the correct one?
Also, please provide your example and the result in text format instead of an image.
To post your example, you could use the XL2BB tool or at least just copy your data range and paste it here.

 

[Adalinda]

@Adalinda
I think I can create a macro to handle this task, but I got a bit confused by the alias part.
Is the second image in post #1 actually the expected result? If not, could you post the correct one?
Also, please provide your example and the result in text format instead of an image.
To post your example, you could use the XL2BB tool or at least just copy your data range and paste it here.

I get an excel that looks like Input. There a list of names (column A-C) but some of them also have Alias names. But they put them in Column D in this format {Last Name}, {First Name} {Middle Name(s)}. They sometimes have one Alias Name but more often there are 2-5 and sometimes they have multiple middle names. I will go in manually and insert a row below the "Main Name" and copy/paste the Alias name(s) into their proper columns (A-C). Once I have added all the Alias names under the "Main Name" I then copy columns E-F and paste them into the new Alias name rows. The end product I get looks like the Output image

Macro Play.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Input							Output
2	First Name	Middle Name	Last Name	Alias	DOB	Additional		First Name	Middle Name	Last Name	Alias	DOB	Additional
3	Jennifer		Davis	Miller, Jennifer ; Wilcox, Jennifer M	9/13/2008	WASHINGTON		Jennifer		Davis		9/13/2008	WASHINGTON
4	Swift		Tony		4/11/2015	BENTON		Jennifer		Miller		9/13/2008	WASHINGTON
5	Carmen	Shera	Planter		10/30/1982	PHILLIPS		Jennifer	M	Wilcox		9/13/2008	WASHINGTON
6	Carmen		Planter		10/30/1982	CONWAY		Swift		Tony		4/11/2015	BENTON
7	Linda		Gulley	Mccolly, Linda ; Williams, Lenny ; Robertson, Lenny	6/25/1991	BENTON		Carmen	Shera	Planter		10/30/1982	PHILLIPS
8	Jeremiah		Munroe	Monroe, Jeremiah ; Baldrew, Jeremiah	5/14/1987	LONOKE		Carmen		Planter		10/30/1982	CONWAY
9	Jeremiah		Munroe	Monroe, Jeremiah ; Baldrew, Jeremiah	5/14/1986	BENTON		Linda		Gulley		6/25/1991	BENTON
10	Robert		Doe		3/23/2008	LONOKE		Linda		Mccolly		6/25/1991	BENTON
11	John	Jacob	Jingle		8/22/1996	CONWAY		Lenny		Williams		6/25/1991	BENTON
12	Kevin	Anthony	Valdez		5/19/2002	LONOKE		Lenny		Robertson		6/25/1991	BENTON
13	Kevin		Mockingbird		5/19/2002	PHILLIP		Jeremiah		Munroe		5/14/1987	LONOKE
14								Jeremiah		Monroe		5/14/1987	LONOKE
15								Jeremiah		Baldrew		5/14/1987	LONOKE
16								Jeremiah		Munroe		5/14/1986	BENTON
17								Jeremiah		Monroe		5/14/1986	BENTON
18								Jeremiah		Baldrew		5/14/1986	BENTON
19								Robert		Doe		3/23/2008	LONOKE
20								John	Jacob	Jingle		8/22/1996	CONWAY
21								Kevin	Anthony	Valdez		5/19/2002	LONOKE
22								Kevin		Mockingbird		5/19/2002	PHILLIP
Sheet1

 

[Fuji]

First, delete 1st row that you have "Input" & "Output" then run the code

Sub test()
    Dim a, b, e, i&, ii&, n&, x
    a = [a1].CurrentRegion.Value
    ReDim b(1 To UBound(a, 1) * 10, 1 To UBound(a, 2))
    For i = 2 To UBound(a, 1)
        n = n + 1: x = Split(a(i, 4), ";"): a(i, 4) = ""
        For ii = 1 To UBound(a, 2)
            b(n, ii) = a(i, ii)
        Next
        If UBound(x) > -1 Then
            For Each e In x
                n = n + 1
                For ii = 1 To UBound(a, 2)
                    b(n, ii) = a(i, ii)
                Next
                b(n, 1) = Trim$(Split(e & ",", ",")(0))
                b(n, 2) = Trim$(Split(e & ",", ",")(1))
            Next
        End If
    Next
    With [h1].Resize(, UBound(b, 2))
        .CurrentRegion.ClearContents
        .Value = a
        .Rows(2).Resize(n).Value = b
    End With
End Sub

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[lrobbo314]

No one was even going to respond and I'm just getting ignored. Cool. At least Alan gives a shout out to PQ.

 

[Adalinda]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

You are amazing! Thank you for this info. I am trying to learn how to do macros and more data manipulation but its all self teaching. LOL

 

[Adalinda]

No one was even going to respond and I'm just getting ignored. Cool. At least Alan gives a shout out to PQ.

Sorry I thought I did respond. I just put it as a new comment not a reply. I have been trying to figure out how to test out your code since I didn't know what PQ was. With Alan's info/suggestions I can hopefully figure it out. Again thank you for your help. I really appreciate it.