Using Power Query, here is the Mcode. Bring into PQ, and pivot the data being sure to Not Aggregate the Data.
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"First Name", type text}, {"Last Name", type text}, {"Status", type text}, {"Benefit", type text}, {"Plan", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Benefit]), "Benefit", "Plan")
in
#"Pivoted Column"
v |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
L |
1 |
ID |
First Name |
Last Name |
Status |
Medical |
Vision |
Dental |
Life |
Extra Life |
LTD |
EAP |
STD |
2 |
1 |
Joe |
Doe |
Active |
PPO |
EyeQue |
TeethBrite |
|
|
|
|
|
3 |
2 |
Jane |
Doe |
Active |
|
|
|
Dying Payment |
Paid Life |
Sickness |
|
|
4 |
3 |
John |
Doe |
Active |
|
|
|
|
|
|
EAP |
Short Sickness |