Convert info to matrix

[Tigerexcel]

Okay, I can do this with a PT, wondering how you would do it with formulae/PQ. Doing this for educational purposes only.

Book3
	A	B	C	D	E	F	G	H	I	J
1	Name	Subject	Score			Maths	Chem	English	Geog	History
2	Jack	Maths	95		Jack	95		62
3	Bert	Chem	51		Bert		51
4	Jack	English	62		Steve	85			45
5	Steve	Maths	85		Jenny	87
6	Jenny	Maths	87		Mary			84
7	Mary	English	84		Vic		60
8	Steve	Geog	45		Jake					100
9	Vic	Chem	60
10	Jake	History	100
Sheet1

Information comes in as above (A to C), I need it to be as in columns E to J.

 

[JoeMo]

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Name	Subject	Score			Maths	Chem	English	Geog	History
2	Jack	Maths	95		Jack	95		62
3	Bert	Chem	51		Bert		51
4	Jack	English	62		Steve	85			45
5	Steve	Maths	85		Jenny	87
6	Jenny	Maths	87		Mary			84
7	Mary	English	84		Vic		60
8	Steve	Geog	45		Jake					100
9	Vic	Chem	60
10	Jake	History	100
Sheet4

Cell Formulas
Range	Formula
F2:J8	F2	=IFERROR(INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10=$E2)*($B$2:$B$10=F$1),0)),"")

 

[JoeMo]

Just noticed you use Excel 365. Here's another way uisng the FILTER function.

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Name	Subject	Score			Maths	Chem	English	Geog	History
2	Jack	Maths	95		Jack	95		62
3	Bert	Chem	51		Bert		51
4	Jack	English	62		Steve	85			45
5	Steve	Maths	85		Jenny	87
6	Jenny	Maths	87		Mary			84
7	Mary	English	84		Vic		60
8	Steve	Geog	45		Jake					100
9	Vic	Chem	60
10	Jake	History	100
Sheet4 (2)

Cell Formulas
Range	Formula
F2:J8	F2	=FILTER($C$2:$C$10,($A$2:$A$10=$E2)*($B$2:$B$10=F$1),"")

 

[AhoyNC]

With Excel 365 you could use the FILTER function.
Drag formula in F2 down and across.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Subject	Score			Maths	Chem	English	Geog	History
2	Jack	Maths	95		Jack	95		62
3	Bert	Chem	51		Bert		51
4	Jack	English	62		Steve	85			45
5	Steve	Maths	85		Jenny	87
6	Jenny	Maths	87		Mary			84
7	Mary	English	84		Vic		60
8	Steve	Geog	45		Jake					100
9	Vic	Chem	60
10	Jake	History	100
Sheet1

Cell Formulas
Range	Formula
F2:J8	F2	=FILTER($C$2:$C$10,($A$2:$A$10=$E2)*($B$2:$B$10=F$1),"")

 

[Tetra201]

With PQ, try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ReordC = Table.ReorderColumns(Source,{"Subject", "Name", "Score"}),
    PivotC = Table.Pivot(ReordC, List.Distinct(ReordC[Subject]), "Subject", "Score", List.Sum)
in
    PivotC

 

[Tigerexcel]

Thanks everyone, great responses and nice succinct solutions.

 

[Dossfm0q]

Filter Vertical / Horizontal Headers

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Subject	Score			Maths	Chem	English	Geog	History
2	Jack	Maths	95		Jack	95		62
3	Bert	Chem	51		Bert		51
4	Jack	English	62		Steve	85			45
5	Steve	Maths	85		Jenny	87
6	Jenny	Maths	87		Mary			84
7	Mary	English	84		Vic		60
8	Steve	Geog	45		Jake					100
9	Vic	Chem	60
10	Jake	History	100
11
Sheet1

Cell Formulas
Range	Formula
F1:J1	F1	=IFERROR(INDEX($B$2:$B$10,AGGREGATE(15,6,MATCH($B$2:$B$10,$B$2:$B$10,0)/(MATCH($B$2:$B$10,$B$2:$B$10,0)=(ROW($B$2:$B$10)-ROW($B$2)+1)),COLUMNS($F$1:F1))),"")
F2:J10	F2	=IFERROR(INDEX($C$2:$C$10,AGGREGATE(14,6,(ROW($A$2:$A$10)-ROW($A$2)+1)/($A$2:$A$10=$E2)/($B$2:$B$10=F$1),1)),"")
E2:E8	E2	=IFERROR(INDEX($A$2:$A$10,AGGREGATE(15,6,MATCH($A$2:$A$10,$A$2:$A$10,0)/(MATCH($A$2:$A$10,$A$2:$A$10,0)=(ROW($A$2:$A$10)-ROW($A$2)+1)),ROWS($A$2:A2))),"")

10=$E2)/($B$2:$B$10=F$1),1)),"")[/XD][/XR][/RANGE]