Split items listed in a single cell to multiple rows

[bjcf33183]

Hi folks,
I'm looking to find the best way of going about taking contents from a bunch of single cells and output them to multiple rows.

Column A - Ticket Number
Column B - Application Name
Column C - User Name(s)

Column A and B always contain a single value per cell. Each cell in Coumn C, however, contains 1 or more User Names. The ones that contain multiple names are formatted in such a way that each User Name is on the next line - but all within a single cell.
Below is an example.. The first row, for example, contains 3 distinct User Names in Column C; the second row only contains 1; third row contains 4
Since there's a total of 8 User Names between those three rows, my desired result is to have them split out into 8 rows
Thoughts?

Current Format:

Ticket #	Application Name	Username(s)
P100	Finance	jdoe csmith ajohnson
P101	Payroll	jjones
P102	Finance	rsmith aking2 fmoore lparker

<tbody>
</tbody>

Desired Result:

Ticket #	Application Name	Username(s)
P100	Finance	jdoe
P100	Finance	csmith
P100	Finance	ajohnson
P101	Payroll	jjones
P102	Finance	rsmith
P102	Finance	aking2
P102	Finance	fmoore
P102	Finance	lparker

<tbody>
</tbody>

 

[Ron Coderre]

An excellent option is Power Query (if you're open to using it).

I put your sample data in cells A1:C4 and named that range "DataRange"
Then....with a cell in that range selected...
- Power Query.From_Table/Range

Here's the complete Power Query code I used:

let
    Source = Excel.CurrentWorkbook(){[Name="DataRange"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Username(s)", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Username(s).1", "Username(s).2", "Username(s).3", "Username(s).4"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Ticket #", "Application Name"}, "Attribute", "UserName(s)"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

The result, if you load it to a table in a worksheet, is:

Ticket #      Application Name UserName(s)
P100          Finance          jdoe
P100          Finance          csmith
P100          Finance          ajohnson
P101          Payroll          jjones
P102          Finance          rsmith
P102          Finance          aking2
P102          Finance          fmoore
P102          Finance          lparke

Is that something you can work with?

 

[Ron Coderre]

SOMETIMES...I post the wrong version of my proposed solution...
THIS was one of those times.

Here's what I intended (PQ code is more concise)
An excellent option is Power Query (if you're open to using it).

I put your sample data in cells A1:C4 and named that range "DataRange"
Then....with a cell in that range selected...
- Power Query.From_Table/Range

Here's the complete Power Query code I used:

let
    Source = Excel.CurrentWorkbook(){[Name="DataRange"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Promoted Headers", {{"Username(s)", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Username(s)")
in
    #"Split Column by Delimiter"

The result, if you load it to a table in a worksheet, is:

Ticket #      Application Name UserName(s)
P100          Finance          jdoe
P100          Finance          csmith
P100          Finance          ajohnson
P101          Payroll          jjones
P102          Finance          rsmith
P102          Finance          aking2
P102          Finance          fmoore
P102          Finance          lparke

Is that something you can work with?

 

[JLGWhiz]

Based on the example in Post #1 , it is assumed that the names in column C are entered using Alt + Enter in order to stack them vertically. If so, then this code should work. If not, the code fails.

Sub t()
Dim c As Range, spl As Variant, i As Long
    With ActiveSheet
        For Each c In .Range("C2", .Cells(Rows.Count, 3).End(xlUp))
            spl = Split(c.Value, vbLf)
            If UBound(spl) > 0 Then
                c.Offset(1).Resize(UBound(spl)).EntireRow.Insert
                For i = LBound(spl) To UBound(spl)
                    c.Offset(1, -2).Resize(UBound(spl), 2) = c.Offset(, -2).Resize(, 2).Value
                    c.Offset(i) = spl(i)
                Next
            End If
        Next
    End With
End Sub

 

[bjcf33183]

Thank you both!

 

[JLGWhiz]

Thank you both!

Glad we could help,
Regards, JLG