Looking for a Formula / VBA that adds and transposes project cost and phase items

[kabutocat]

So I have a table that has the following

Phase	Code	Description	Cost
Preliminary Phase
	A1	Liaison	$ 3,000.00
	A2	Project Meeting	$ 2,000.00
	B1	Financial Modelling	$ 1,000.00
	C1	Project Management	$ 1,000.00
Main Phase
	A1	Liaison	$ 5,000.00
	A2	Project Meeting	$ 4,000.00
	B1	Financial Modelling	$ 2,000.00
	C1	Project Management	$ 3,000.00
End Phase
etc

I'm looking for a formula or VBA that would output the following

Code	Description	Preliminary Phase	Main Phase	End Phase
A1	Liaison	$ 3000.00	$ 5000.00	etc.
A2	Project Meeting	etc	etc
B1	Financial Modelling
C1	Project Management

It would be preferred if the formula / vba can be flexible with additional items and phases.
Thank you.

 

[alansidman]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Phase"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Code] <> null)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Phase", type text}, {"Code", type text}, {"Description", type text}, {"Cost", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Phase]), "Phase", "Cost")
in
    #"Pivoted Column"

Book8
	A	B	C	D	E	F	G	H	I
1	Phase	Code	Description	Cost		Code	Description	Preliminary Phase	Main Phase
2	Preliminary Phase					A1	Liaison	3000	5000
3		A1	Liaison	$3,000.00		A2	Project Meeting	2000	4000
4		A2	Project Meeting	$2,000.00		B1	Financial Modelling	1000	2000
5		B1	Financial Modelling	$1,000.00		C1	Project Management	1000	3000
6		C1	Project Management	$1,000.00
7	Main Phase
8		A1	Liaison	$5,000.00
9		A2	Project Meeting	$4,000.00
10		B1	Financial Modelling	$2,000.00
11		C1	Project Management	$3,000.00
Sheet1