how to show data based on 2 column data criteria

[sonosite]

I'm trying to create a formula that will transpose the data as above based on 2 values (I have data provided as shown on the upper half, but I need the data presented as shown on the bottom half). Person AA has 1 car, 2 bike and 3 van but person BB only has 7 SUV and 8 truck. Any advice on how to do this?

 

[sandy666]

try Power Query (Get&Transform)

person	item	qty		person	car	bike	van	SUV	truck
AA	car	1		AA	1	2	3
	bike	2		BB				7	8
	van	3		CC	9		6	12
BB	SUV	7
	truck	8
CC	car	9
	SUV	12
	van	6

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FillD = Table.FillDown(Source,{"person"}),
    Pivot = Table.Pivot(FillD, List.Distinct(FillD[item]), "item", "qty")
in
    Pivot