How can I effectively parse a Google Spreadsheet with data not aligned well within Columns and Rows?

Question

I have a data export in a very inconvenient format. In sheet1, I have student grade data that I cannot figure out how to extract into sheet2, where I am attempting to separate and SUM grades in one column IF they are designated with HL or SL in another column. However, the student name sits in a set of merged cells one row above the data and separated by blank rows.

I believe it could be done with =FILTER and =QUERY, but these are not my strengths.

If you are willing to help: Please see the anonymized example of the aforementioned two sheets.

In sheet2 the desired data and a stab at the formulas can be found.

With great appreciation and apologies if I've left anything out.

Answer 1

Tag data with Student Name

In sheet1, insert a helper column A and add the following formula in cell A7 and copied to rows below:

=if(B7<>"",if(B6="Advisor",B5,A6),"")

Helper Column

Calculations

---

Named Ranges

To make the formulas shorter in length and easier to follow, I used named the ranges. You could alternatively use the A1 range addresses

Name	Range
helper	Sheet1!A:A
advisor	Sheet1!B:B
average	Sheet1!K:K
level	Sheet1!G:G
grade	Sheet1!L:L
beCols	Sheet1!N:O
names *	Sheet2!A2:A     * the filtered list on Sheet2

Formulas

Multiple Formulas
Sheet2!A2  -  Student SORT(UNIQUE(FILTER(helper,helper<>"")),1,1)
Sheet2!B2  -  Advisor =BYROW(names, LAMBDA(x, IF(x<>"", INDEX(advisor,MATCH(x,helper, 0)),)))
Sheet2!C2  -  HL Sum =BYROW(names, LAMBDA(x, IF(x<>"", SUMIFS(grade,helper,x,level,"HL"),)))
Sheet2!D2  -  SL Sum =BYROW(names, LAMBDA(x, IF(x<>"", SUMIFS(grade,helper,x,level,"SL"),)))
Sheet2!E2  -  BE Count =BYROW(names, LAMBDA(x, IF(x<>"", COUNTIF(filter(beCols, helper=x),"BE"),)))

Depending on what you want, you can combine all the formulas in a single formula.

Single Formula

In Sheet2!A2, place

     =BYROW(SORT(UNIQUE(FILTER(helper,helper<>"")),1,1),  
      LAMBDA(x, IF(x<>"", {
        INDEX(names,MATCH(x,names,0)), 
        INDEX(advisor,MATCH(x,helper,0)),
        SUMIFS(grade,helper,x,level,"HL"),
        SUMIFS(grade,helper,x,level,"SL"),
        COUNTIF(filter(beCols, helper=x),"BE")},)))

If you want to generate the column labels as well you can add them into the formula

={ "Student","Advisor","HL Sum","SL Sum","BE Count";
    [existing formula here] 
 }      

Images

---

Single Formula w/ Labels

Student Formula

Advisor Formula

HL Formula

SL Formula

BE Formula

Answer 2

Delete rows 1-4 at the top of Sheet1 and any blank rows at the bottom, then try the following in another sheet:

=lambda(oldtbls,header,lambda(students,lambda(newtbl,
{query(newtbl,"select Col1,Col2,sum(Col12) group by Col1,Col2 pivot Col7",1),{"BE";index(sort({unique(filter(students,students<>"")),ifna(vlookup(unique(filter(students,students<>"")),query(newtbl,"select Col1,count(Col1) where Col14='BE' or Col15='BE' group by Col1",1),2,0),0)}),,2)}})(
{{"Student",header};filter({students,oldtbls},students<>"")}))(
scan(,index(oldtbls,,1),lambda(a,r,iferror(if(offset(r,-1,0)="Advisor",offset(r,-2,0),if(r="",r,a)),)))))(
Sheet1!$A:$O,
Sheet1!$2:$2)

This should work if the number of subjects changes from student to student. I'm not going to try to explain all this, but in essence we are creating a flat table and then running the necessary queries from it.