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?
- Thx. for your reply
I will try this formula when I come back home.
I appreciate too much your efforts.
