Forum Discussion
Filter data and construct table
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
ajl_ahmed I think you have some text or spaces instead of blanks that was throwing it off. see attached.
=LET(students,'P2'!$RO$7:$RO$62, semAtry2,'P2'!$RZ$7:$SM$62, semBtry2,'P2'!$SQ$7:$TD$62, semAtry1,'P2'!$TT$7:$UG$62, semBtry1,'P2'!$UK$7:$UX$62, semAres2,'P2'!$SO$7:$SO$62, semBres2,'P2'!$TF$7:$TF$62, semAres1,'P2'!$UI$7:$UI$62, semBres1,'P2'!$UZ$7:$UZ$62, stage, 'P2'!$RZ$1, semAsubjTitles,'P2'!$TT$5:$UG$5, semBsubjTitles,'P2'!$UK$5:$UX$5, studentCount, ROWS(students), subjAcount, COLUMNS(semAsubjTitles), subjBcount, COLUMNS(semBsubjTitles), totSubjCount, subjAcount+subjBcount, maxRows, studentCount*totSubjCount*2, fulltable,MAKEARRAY(maxRows,7,LAMBDA(rr,cc,CHOOSE(cc, stage, INDEX(students,1+INT((rr-1)/totSubjCount/2)), CHOOSE(1+(MOD(rr-1,totSubjCount)>=subjAcount), "First", "Second"), LET(ss,MOD(rr-1,totSubjCount)+1, IF(ss<=subjAcount, INDEX(semAsubjTitles,ss), INDEX(semBsubjTitles,ss-subjAcount))), LET(ss,MOD(rr-1,2*totSubjCount)+1, IF(ss<=subjAcount, INDEX(semAtry1, 1+INT((rr-1)/totSubjCount/2), ss), IF(ss<=totSubjCount,INDEX(semBtry1, 1+INT((rr-1)/totSubjCount/2), ss-subjAcount), IF(ss<=totSubjCount+subjAcount, INDEX(semAtry2, 1+INT((rr-1)/totSubjCount/2), ss-totSubjCount), INDEX(semBtry2, 1+INT((rr-1)/totSubjCount/2), ss-totSubjCount-subjAcount))))), IF(MOD(rr-1,totSubjCount)<subjAcount, INDEX(semAres2, 1+INT((rr-1)/totSubjCount/2)), INDEX(semBres2, 1+INT((rr-1)/totSubjCount/2))), (MOD(rr-1,2*totSubjCount)>=totSubjCount)+1 ))), passGrade, N(INDEX(fulltable,,5))>=50, failedResult, INDEX(fulltable,,6)="Failed", firstAttempt, INDEX(fulltable,,7)=1, oo,FILTER(fulltable, firstAttempt*failedResult*passGrade+NOT(firstAttempt)*passGrade*failedResult,""), INDEX(oo,sequence(rows(oo)),sequence(,5)) )this should work. Basically line 26 above will return only the first 5 columns.
On the other hand I like to have that information just in case so I would just 'hide' the column (right click on the column and select hide)
- I don't understand.
Didn't the previous solution do what you need?
You can just use the prior formula and change the ranges for the inputs. I think their names are clear enough.
If you have the list filtered like this you could also just use conditional formatting to HIGHLIGHT the passing grades so you can find them easily. You could even 'hide' the failing grades by making their text the same as the background. mtarler Hi
I collected semester 1 results for the first and second attempts (the blue and yellow area in the attached file)and filtered them for the failed students only (I think this step will simplify the filtering of passed subjects process of the failed students). The student is considered to fail in a semester if it fails in both attempts of a semester. The same thing is done for the second semester. The final result of semester 1 is shown in column (WI) and for semester 2 column (XR) of the attached file. for each subject in semesters 1 and 2, a failed student can have:
- two marks (<50) in the first and second attempt respectively and this mark is not needed in the exception table.
- one mark (>=50) in the first attempt only and this mark and other details related to it is needed in the exception table. while in the second attempt, the mark of this subject should be blank ( blank cell information of the second attempt is not needed in the exception table).
- two marks; one mark (<50) in the first attempt and another mark (>=50) in the second attempt respectively. the mark (>=50) is needed in the exception table.
Could you please filter sheet P2 of the attached file to collect only subjects that have (mark>=50) and construct the table shown in the exception table sheet? it is better or me to keep the sequence of columns as it is and not change it, please. Many thanks for your efforts and time.
- The same semester subjects are repeated in the second attempt (you can see the subjects range of each semester for first and second attempt) so if
try 1 - pass ..... try 2 - pass (exclude all subjects of this semester) because it is passed from try 2 to new stage.
try 1 - pass ..... try 2 - fail ( if a student passed in the first attempt of this semester ( it could be semester 2 or 2) then no need to apply for second attempt try because it is passed. ( this state must not happened).
try 1 - fail ........ try 2 - pass same in 1
try 1 - fail ........ try 2 - fail filter only passed subjects in try 1 and 2 of a semester. - ok there are 4 possible combinations for the final result:
try 1 - pass ..... try 2 - pass
try 1 - pass ..... try 2 - fail
try 1 - fail ........ try 2 - pass
try 1 - fail ........ try 2 - fail
you say regardless of try 1 if try 2 is pass then ignore, right?
if try 1 AND try 2 is fail then include all subjects with passing grades.
what if try 1 is pass and try 2 is fail? mtarler Thanks ...
if I need to do the following case,
if the final semester result, for example, semester 1, of a student in the second attempt is passed, then I need to exclude all (passed and failed) subjects of this semester (semester 1) in the first attempt (regardless of what is the final result of semester 1- First attempt) from the filtering process.
The same thing for semester 2, if the Final semester result in the second attempt is passed, then I need to exclude all (passed and failed) subjects of this semester (semester 2) in the first attempt (regardless of what is the final result of semester 2- First attempt) from the filtering process. Is it possible to do this scenario? many thanksI appreicate your efforts Mtarler
I have checked the output of the formula for students 1,3 and 4 and I noticed that it is not included all the passed subjects.
For student 1, the formula missed the subjects of the second semester, the second attempt.
For student 3, the formula missed the subjects of the first semester, the second attempt.
For student 4, the formula missed the subjects of the first and the second semesters (the second attempt)
Also, I noticed that the attempt column should be reversed (1 instead of 2 and 2 instead of 1).
just quick notes:
- The filter excludes any student who passed in the first semester or the second semester or both in the first attempt or in the second attempt.
-The filter is dedicated to the failed students in the first attempt and the second attempt and filtered only the passed subjects for the failed students in any attempt.
- The condition of specifying the student is failed in a subject that the degree should be greater than or equal to 50.
Hope to help me to solve this issue. Many thanks again
