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 getting a subject mark less than 50 in semesters 1 and 2 respectively and stored them in another range (Range 3). 

Range 3 should also include other information (Name, Stage, Semester, Subject) as shown in range 3. 

Anyone can help me to construct a formula or code to do that. 

I have attached an excel file 365 explaining my question. 

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