Pivot for a representation

Question

       A               B          B    
   1 School ID    Parameter   Score
   2 Sch109          P1       20
   3 Sch109          P2       40       
   4 Sch109          P3       50
   5 Sch109                   110  #Sum of P1, P2,P3 For Sch109
   6 Sch210          P1       10
   7 Sch210          P2       30       
   8 Sch210          P3       90
   9 Sch210                   130   #Sum of P1, P2,P3 For Sch210
   10 Sch100         P1       5
   11 Sch100         P2       20       
   12 Sch100         P3       15
   13 Sch100                  40    #Sum of P1, P2,P3 For Sch100
    
    AND SO ON

So basically in this excel all schools have parametersP1, P2, P3 and one row where no parameter is mentioned which is sum of all parameters (P1+P2+P3) which is also shown in comments by #. I need output such that all parameters for one school are in one line.

Expected output:

 A             B    C   D   E
1 School ID   P1   P2   P3  Score 
2 Sch109      20   40   50  110
3 Sch210      10   30   90  130
4 Sch100      5    20   15  40
5

and so on.

How to do this in excel. With Pivot also I am unable to get desired result

Answer 1

If you have Excel365 then you can do it by FILTER() and XLOOKUP() formula.

 =FILTER($C$2:$C$13,($A$2:$A$13=$F2)*($B$2:$B$13=G$1),XLOOKUP($F2,$A$2:$A$13,$C$2:$C$13,"",0,-1))

Edit: Non 365 formula.

For your given dataset you can use INDEX()/MATCH() combination. To get P1, P2, P3 value per school use following formula to G2 cell.

=INDEX($C$2:$C$13,MATCH($F2&G$1,$A$2:$A$13&$B$2:$B$13,0))

To get Score data use below formula to J2 cell.

=INDEX($C$2:$C$13,MATCH($F2&"",$A$2:$A$13&$B$2:$B$13,0))

If you want to combine above two formula into one then use IFEEROR() and use full formula like-

=IFERROR(INDEX($C$2:$C$13,MATCH($F2&G$1,$A$2:$A$13&$B$2:$B$13,0)),INDEX($C$2:$C$13,MATCH($F2&"",$A$2:$A$13&$B$2:$B$13,0)))

To understand how the formula working then please split each formula to individual cells then observer result one by one.