Split Rows to column or vlookup withTranspose columns

[msvictorialc]

1 workbook, 2 Tabs.
tab 1 is raw data with multiple rows and columns.

tab 2 is where I want to simplify the data, 1 Row per account with dataspreading out in columns instead of rows of partial duplicates.

tab 1
A B
1. 1234 Follow up
2. 1233. Follow up
3. 1233. Missing PW
4. 1233. Review 2/12
5. 1232. Called
6. 1231. Follow up

Tab 2 without Duplicate acct #’s
1. Acct#. Action. Action. Action
2. 1234. Follow up
3. 1233. Follow up. Missing PW Review 12/12
4. 1232. Called
5. 1231. Follow up

 

[kevatarvind]

Please Try below code make copy of your workbook before try this code

Sub Split_Rows_To_Columns()
    Dim d As Object, c As Range
    Dim l, tmp As String, lr, lc As Long, i, j, k As Integer
    Dim DSheet As Worksheet
    Dim RSheet As Worksheet
    Application.ScreenUpdating = False
    Set DSheet = Sheets("TAB1") ' Chnage Raw Data Sheet name with yours Sheet Name
    Set RSheet = Sheets("TAB2") ' Chnage Result Sheet name with yours Sheet Name
    DSheet.Select
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Set d = CreateObject("scripting.dictionary")
    For Each c In Range("A2:A" & lr)
        tmp = Trim(c.Value)
        If Len(tmp) > 0 Then d(tmp) = d(tmp) + 1
    Next c
    j = RSheet.Range("A" & Rows.Count).End(xlUp).Row + 1
    RSheet.Cells(1, 1).Value = "Act"
    For Each l In d.keys
        k = 1
        RSheet.Cells(j, k).Value = l
        For i = 2 To lr
            If Cells(i, 1).Value & "A" = l & "A" Then
                If Application.WorksheetFunction.CountIf(RSheet.Range("A" & j, "XFD" & j), Cells(i, 2).Value) = 0 Then
                    RSheet.Cells(j, k + 1).Value = Cells(i, 2).Value
                    RSheet.Cells(1, k + 1).Value = "Action"
                    k = k + 1
                End If
            End If
        Next
    j = j + 1
    Next
    Application.ScreenUpdating = True
End Sub

 

[sandy666]

another way with Power Query (Get&Transform)

Acct#	Action		Acct#	Action
1234	Follow up		1234	Follow up
1233	Follow up		1233	Follow up, Missing PW, Review 2/12
1233	Missing PW		1232	Called
1233	Review 2/12		1231	Follow up
1232	Called
1231	Follow up

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Acct#"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Action", each Table.Column([Count],"Action")),
    Extract = Table.TransformColumns(List, {"Action", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    Extract

or

Acct#	Action		Acct#	Action.1	Action.2	Action.3
1234	Follow up		1234	Follow up
1233	Follow up		1233	Follow up	Missing PW	Review 2/12
1233	Missing PW		1232	Called
1233	Review 2/12		1231	Follow up
1232	Called
1231	Follow up

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Acct#"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Action", each Table.Column([Count],"Action")),
    Extract = Table.TransformColumns(List, {"Action", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    Split = Table.SplitColumn(Extract, "Action", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Action.1", "Action.2", "Action.3"})
in
    Split

 

[sandy666]

or more flexible for the second case

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Acct#"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Action", each Table.Column([Count],"Action")),
    Extract = Table.TransformColumns(List, {"Action", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    MaxCount = List.Max(Table.AddColumn(Extract, "SCount", each List.Count(Text.Split([Action],",")))[SCount]),
    Split = Table.SplitColumn(Extract, "Action", Splitter.SplitTextByDelimiter(", "), MaxCount)
in
    Split