Sorting Data across multiple columns

[karmaimages]

Evening,

I have a report that is produced from a front end system where names are entered with a comma separating them, some have 1 name, some 2 names some 3 or 4

for example:

Jo Blogs, John Smith
Shaun Green
Betty Jones, Sharon Blue, Harry Grey

Is there an easy way to separate the names into one list so they can be used easier?

 

[Legacy 436357]

Under the Data tab use text to columns and choose deliminated.

 

[Legacy 436357]

You can use this macro to sort the names into one column. Highlight the range and then run the code:

<code>Sub MakeOneColumn()

    Dim vaCells As Variant
    Dim vOutput() As Variant
    Dim i As Long, j As Long
    Dim lRow As Long

    If TypeName(Selection) = "Range" Then
        If Selection.Count > 1 Then
            If Selection.Count <= Selection.Parent.Rows.Count Then
                vaCells = Selection.Value

                ReDim vOutput(1 To UBound(vaCells, 1) * UBound(vaCells, 2), 1 To 1)

                For j = LBound(vaCells, 2) To UBound(vaCells, 2)
                    For i = LBound(vaCells, 1) To UBound(vaCells, 1)
                        If Len(vaCells(i, j)) > 0 Then
                            lRow = lRow + 1
                            vOutput(lRow, 1) = vaCells(i, j)
                        End If
                    Next i
                Next j

                Selection.ClearContents
                Selection.Cells(1).Resize(lRow).Value = vOutput
            End If
        End If
    End If

End Sub

</code>

 

[sandy666]

try with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Names[/td][td][/td][td=bgcolor:#70AD47]Names[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Jo Blogs, John Smith[/td][td][/td][td=bgcolor:#E2EFDA]Jo Blogs[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Shaun Green[/td][td][/td][td]John Smith[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Betty Jones, Sharon Blue, Harry Grey[/td][td][/td][td=bgcolor:#E2EFDA]Shaun Green[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Betty Jones[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]Sharon Blue[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Harry Grey[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Names", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Names")
in
    #"Split Column by Delimiter"[/SIZE]

 

[Legacy 436357]

My solution is poor I suggest using Sandy's