Forum Discussion
Filter data and construct table
Thx. for your assistance. It is a useful formula. I just have two things about it:1. is it possible to write the name of subjects (Sub.1, sub.2,.......) instead of (1,2,3,......)?2. is it possible to write in the semester field (First) instead of 1 and (second) instead of(2)?Thx. again
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.
- last question matarler
If there is another condition to do the filtering process, (the number of subjects less than 50 are not exceeding 2 subjects only), can we add this condition besides the initial condition <50?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.
- I really appreciate too much your hardworking. many many thanks .... All the best in your life