Forum Discussion

ajl_ahmed's avatar
ajl_ahmed
Iron Contributor
Jul 16, 2022
Solved

Need formula to filter student information based on a condition of getting subject mark less than 50

Hi I have two ranges of data, Ranges 1 and 2, which represent the student marks in the first and second semesters respectively.  I want to collect or filter the students of ranges 1 and 2 based on ...
excel
Formulas and Functions
office 365
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 16, 2022

ajl_ahmed 

This is an Excel Dynamic Array formula, but it will probably look as alien as the Power Query solution!

WorksheetFormula
= VSTACK(
      StudentFilterλ(Range1),
      StudentFilterλ(Range2)
  ):


StudentFilterλ
=LAMBDA(dataRange,
    LET(
        markRange, DROP(dataRange, 2, 1),
        marks,     TOCOL(markRange),
        student,   TOCOL(IF(markRange > 0, DROP(TAKE(dataRange, , 1), 2))),
        subject,   TOCOL(IF(markRange > 0, DROP(TAKE(dataRange, 2), 1, 1))),
        stage,     TOCOL(IF(markRange > 0, INDEX(dataRange, 1, 4))),
        semester,  TOCOL(IF(markRange > 0, INDEX(dataRange, 1, 2))),
        table,     HSTACK(student, stage, semester, subject),
        FILTER(table, marks < 50)
    )
)

The major catch is that you need to set your Excel 365 to the Insider beta channel to have access to the array shaping functions.

 

ajl_ahmed's avatar
ajl_ahmed
Iron Contributor
Jul 20, 2022

PeterBartholomew1 

 

I have an excel file attached below and I want to apply the formula which you sent me here before, to it. is that possible?

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

Resources