Converting Multiple Rows into One

[jburns309]

I need to reorganize data output as listed below:

Source export:
Column A | Column B
Patient A | Diagnosis Code 1
Patient A | Diagnosis Code 2
Patient A | Diagnosis Code 3

Ideal Output:
Column A | Column B | Column C | Column D
Patient A | Diagnosis Code 1 | Diagnosis Code 2 | Diagnosis Code 3

I'm new to VBA, so I'll need something easy to decipher.
Thank you!

 

[sandy666]

you can try PowerQuery aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Patient[/td][td=bgcolor:#5B9BD5]Diagnose[/td][td][/td][td=bgcolor:#70AD47]Patient[/td][td=bgcolor:#70AD47]Diagnose.1[/td][td=bgcolor:#70AD47]Diagnose.2[/td][td=bgcolor:#70AD47]Diagnose.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Patient A [/td][td=bgcolor:#DDEBF7] Diagnosis Code 1[/td][td][/td][td=bgcolor:#E2EFDA]Patient A[/td][td=bgcolor:#E2EFDA]Diagnosis Code 1[/td][td=bgcolor:#E2EFDA]Diagnosis Code 2[/td][td=bgcolor:#E2EFDA]Diagnosis Code 3[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Patient A [/td][td] Diagnosis Code 2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Patient A [/td][td=bgcolor:#DDEBF7] Diagnosis Code 3[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Patient"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Diagnose", each Table.Column([Count],"Diagnose")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Diagnose", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Diagnose", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Diagnose.1", "Diagnose.2", "Diagnose.3"})
in
    #"Split Column by Delimiter"[/SIZE]