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



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. 

7 Replies
best response confirmed by ajl_ahmed (Contributor)


That could be done with Power Query, please check in attached file.


What is Power Query? is it an excel formula?

Hi @ajl_ahmed 

What is Power Query?


Another Power Query option attached. Query code (edited):

    Source = Excel.CurrentWorkbook(),
    CombinedTables = Table.Combine(
        List.Transform({"1".."2"}, each 
                table = Source{[Name="range"&_]}[Content],
                source = Table.PromoteHeaders(Table.Skip(table), [PromoteScalars=true]),
                stage = Table.AddColumn(source, "Stage", each Table.First(table)[Column4])
            in Table.AddColumn(stage, "Semester", each Table.First(table)[Column2])
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(CombinedTables,
        {"Name", "Stage", "Semester"}, "Subject", "Value"
    FilteredRows = Table.SelectRows(UnpivotedOtherColumns, each [Value] < 50),
    RemovedColumn = Table.RemoveColumns(FilteredRows,{"Value"})



Does this method need an internet connection? where can I find your formula or code? I should learn power query from scratch it looks useful, any material help me?


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


        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.


@Peter Bartholomew 


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.