Filter data and construct table

I have an excel file attached below and I want a formula to filter the students who got marks less than 50 and arrange them in a table. below is a description of the attached file Just a quick revi...

Formal table.xlsx32 KB

excel

Excel on Mac

Formulas and Functions

Macros and VBA

office 365

mtarler
Jul 22, 2022
ajl_ahmed updated. For the Semester I 'hard coded' the "First" and "Second" but for the Subjects I added another input name/range for the Subject Names and indexed that accordingly. hope you like.
Formal table.xlsx48 KB

mtarler

Silver Contributor

Jul 22, 2022

ajl_ahmed Using this formula it doesn't require any names to be defined since I define them all in the LET statement so it should be easy to copy/port to other similar sheets. I also allow different sizes for the number of subjects and students and such and doesn't assume you have those new Beta functions:

=LET(students, 'P2'!$B$7:$B$117, semA,'P2'!M7:Z117, semB,'P2'!AB7:AO117, stage, 'P2'!$M$1,
        studentCount, ROWS(students), subjAcount, COLUMNS(semA), subjBcount, COLUMNS(semB), totSubjCount, subjAcount+subjBcount,
        maxRows, studentCount*totSubjCount,
        fulltable,MAKEARRAY(maxRows,5,LAMBDA(rr,cc,CHOOSE(cc,
                   stage,
                   INDEX(students,1+INT((rr-1)/totSubjCount)),
                   1+(MOD(rr-1,totSubjCount)>=subjAcount),
                   LET(ss,MOD(rr-1,totSubjCount)+1,IF(ss>subjAcount,ss-subjAcount,ss)),
                   LET(ss,MOD(rr-1,totSubjCount)+1,IF(ss<=subjAcount,
                                  INDEX(semA, 1+INT((rr-1)/totSubjCount), ss),
                                  INDEX(semB, 1+INT((rr-1)/totSubjCount), ss-subjAcount)))
                    ))),
        FILTER(fulltable, (INDEX(fulltable,,5)<=50)*(INDEX(fulltable,,5)>0),"")
        )

Formal table.xlsx48 KB