Forum Discussion
Filter data and construct table
ajl_ahmed most things CAN be done, it is just how. lol. In any case here is an update with that additional condition:
=LET(students, 'P2'!$B$7:$B$117, semA,'P2'!M7:Z117, semB,'P2'!AB7:AO117, stage, 'P2'!$M$1, semAsubjTitles, 'P2'!M5:Z5, semBsubjTitles,'P2'!AB5:AO5,
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)),
CHOOSE(1+(MOD(rr-1,totSubjCount)>=subjAcount), "First", "Second"),
LET(ss,MOD(rr-1,totSubjCount)+1,IF(ss>subjAcount,INDEX(semBsubjTitles,ss-subjAcount),INDEX(semAsubjTitles,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)))
))),
listA, FILTER(fulltable, (INDEX(fulltable,,5)<=50)*(INDEX(fulltable,,5)>0),""),
listAnames, INDEX(listA,,2),
counts, SCAN(0, listAnames, LAMBDA(cc,pp, SUM(--(pp=listAnames)))),
FILTER(listA, counts<=4,"")
)Note on LINE 16 it says FILTER(listA, counts<=4, ""). This is the line that filters out students that have more than 4 subject scores <50. I used 4 instead of your suggested 2 because in the sample data there were zero students with less than 4 scores under 50. You can change that 4 any number you want or even make it a cell reference to change it dynamically/easier.
Hi
Hope to be well
You did a formula to filter students based on conditions and construct a table for the resulted filter. Hope you remember that.
Now, I need to do another filter based on another condition. Let me explain the scenario to you
I have two master sheets, one for the first attempt and the second one for the second or supplementary attempt.
the new condition is:
- I need to filter the subjects for the failed students who got marks greater than or equal to 50 in the first attempt for the first and second semesters.
- I need to filter the subjects for the students who got marks greater than or equal to 50 in the second attempt for both semesters for the student who failed in the second attempt. Note that the students who passed in the second attempt of both semesters 1 and 2 must be excluded from the table.
- I need to collect the information that gets in the previous steps in the table shown in sheet P1 of the attached file. The first and second attempt sheets are also shown in the attached file
let explain more;
- The filtering process should exclude students who passed semester 1 or 2 completely either in the first attempt or the second attempt. it means that, if the student failed in the first attempt but it passed in the second attempt (in all subjects of semesters 1 and 2) then this student should be excluded from the filtering process and no need to its information in the table.
- I need to filter (the "First Attempt" and 'second attempt" sheets dynamically for the failed students such that:
in the first attempt sheet, for the student name 1, for example, the student passed in some of the first semester subjects (1,4,5,6 and 14) and passed also in some of the second-semester subjects (1,2, and 5) so the formula is needed to return the subjects of semesters 1 and 2 which are (passed) by the student name 1 in the first semester sheets. in addition, the formula should also be applied on the second attempt sheet to filter the data related to the student name 1 and return only the subjects passed by it, in this case, subject 2 which is passed by the student name 1. The collected information should be organized as shown in the table of (P1) sheet.
Hope it is clear now, If you have something not clear let me know please and many thank for your efforts.
ajl_ahmed Formula is in F3 of that sheet. I added columns for final result and attempt but you could remove them or hide them. The formula is even larger than the last but again starts with a whole set of initial definitions for different ranges and then ends with a FILTER with pretty clear conditions that you should be able to tweak if/how needed.
Many thanks for your assistance
I applied your formula to my file and did some range modification in the (let) function. it works but the formula appears blank cells for some students. I do not know why, Could you please apply this formula to the original file? I made another file compatible with the original file regarding this issue. please start work on column (RN) and do not change it in order to just copy-paste the formula without any modification. The file is attached. and many thanks again