Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Filtering Data

Iron Contributor

Hi

I need to use the FILTER function to filter the range (C6:Q55) of the attached file to get the student name who got degree less the 50 in any defined subject (D4:Q4). The defined subjects should be changed dynamically and not fixed. However, there are listed in a range (D4:Q4). The result is explained in column S of the attached file. 

10 Replies
Here you go, try this:
=FILTER(C6:C55,MMULT(IF(D6:Q55<50,1,0),SEQUENCE(COLUMNS(D4:Q5),1,1,0)))
Thx. for your reply
Does this function update dynamically? I mean that if I change the degree from <50 to >50 the elements will be changed?
best response confirmed by VI_Migration (Silver Contributor)
Solution

Hi @ajl_ahmed

Does this function update dynamically? I mean that if I change the degree from <50 to >50 the elements will be changed? => Why don't you try???

 

A sligthly more concise one:

=FILTER(C6:C55,
  MMULT(--(D6:Q55 < 50),SEQUENCE(COLUMNS(D4:Q5)))
)

 

Good - super

@ajl_ahmed 

This differs more in style than substance.  First, because I would never use a direct cell reference I name the data, headers and row-headers 'marks', 'subjects' and 'students' respectively.  The formula I used is given by

= LET(
    Sumλ,       LAMBDA(x, SUM(x)),
    fails,      SIGN(marks<threshold),
    countFails, BYROW(fails, Sumλ),
    FILTER(students, countFails)
 )

The first line defines the Lambda function 'Sumλ' for later use.  I could have used a defined name or simply used the anonymous Lambda function.  The next line works on the entire array of marks, returning 1 for any fail.  MMULT is a very fast and effective function but is somewhat mathematical so I chose to use the modern Lambda helper function BYROW to present the rows for summation one by one.  Finally, filter is used to remove the student names that have no failed subjects.

@Peter Bartholomew 

Great use of Lambda to solve the problem Peter - nice and easy to follow.

I wish I could learn and propose answers using Lambda but my version of Excel is on semi-annual enterprise and the company does not have access to Lambda functions; plus my co-workers would not have a clue as to how to review my work!

@MY 

Your company may well need some more IT oriented staff to test the new functionality before it is released company-wide.  Your IT department / person could set one or two of you up with the insiders beta version.  I believe that to use the new functionality effectively requires 'retraining one's brain'.

Hi @ajl_ahmed 

If any of the option you've got solves this issue, would you be kind to mark it as solution to help those who search, please? Thanks

@L z. , I marked. If @ajl_ahmed disagree we could change.

Thank you @Sergei Baklan. I wasn't necessarily expecting mine to be marked though

I guess you understand my main concern

1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

Hi @ajl_ahmed

Does this function update dynamically? I mean that if I change the degree from <50 to >50 the elements will be changed? => Why don't you try???

 

A sligthly more concise one:

=FILTER(C6:C55,
  MMULT(--(D6:Q55 < 50),SEQUENCE(COLUMNS(D4:Q5)))
)

 

View solution in original post