Forum Discussion
Filter data and construct table
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),"")
)
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.
- I really appreciate too much your hardworking. many many thanks .... All the best in your life