Forum Discussion
Filter data and construct table
I have an excel file attached below and I want a formula to filter the students who got marks less than 50 and arrange them in a table. below is a description of the attached file
Just a quick review, I want to construct a table as shown in sheet (P1) of the attached file to store all the students who got marks less than 50 in the first and the second semesters. The student record is in sheet (P2) of the attached file and the semester marks are explained in the collared ranges, range M7:Z111 for semester 1 and range AB7:AO111 for semester 2. When filtering is done ;
- I need the student name, subject, stage, and semester shown in the second attempt students table.
- neglecting bank cells but taking into consideration in the filtering process they may be contained data in the future.
- The number of students is variable, it could be 111 students or less.
- I need to draw a border around the dedicated row when a student adds to the table of P1.
- The organization of cells in the attached file is from Right to left direction.
- Is it possible to dynamically resize the table of P1 based on the size of the row data?
-can I copy the constructed table into another excel file in the future?
I appreciate too much your efforts and many thanks in advance for your help.
Regards
24 Replies
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
- Thx. for your reply
I will try this formula when I come back home.
I appreciate too much your efforts.
