1

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.

2
  • Do all students study the same number of subjects? This will influence the approach required. Nov 28, 2022 at 22:53
  • No, the subject number changes. I really should have noted that as it wrecked my initial plan of of counting off multiples. Sorry. Nov 29, 2022 at 8:07

2 Answers 2

0

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.

0

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

3
  • 1
    This worked well to link the student name to each row of data. Thank you! Nov 29, 2022 at 9:52
  • With the addition of the helper column, I was able to compose =QUERY('sheet1'!B5:Q2000,"select B, C, E, H, M, O, P where B is not null"), but still working on summing finals grades for HL independently of SL per student. Nov 29, 2022 at 9:54
  • @ZacharyBlakley I updated the answer with sample formulas that use the helper column. The example formulas are shown individually for each column, and I also showed you them combined in a single formula. Nov 29, 2022 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.