Filter columns with criteria

GMC The Macro Man

Board Regular
Joined
Mar 23, 2023
Messages
64
Office Version
  1. 2021
Platform
  1. Windows
Hello there
The spreadsheet below shows a list of pupils with some of the subjects they are going to sit this year.
The values under the subjects, represent the expected result they will achieve. The scale is 1 = Excellent and 8 = Very Poor.
I needed to filter out (on a separate sheet) any pupil who has a 7 or 8 listed in their expected results.
Thanks to FLUFF (Mr Excel MVP) he was able to assist me using the formula below which worked perfectly.
=LET(f,FILTER(Grading!A2:N11,MMULT(--(Grading!D2:N11>=7),SEQUENCE(COLUMNS(Grading!D1:N1),,,0))),IF(f="","",f))

The client has now asked if they could add another column after each subject with a Grading value of either 4,5,6 or 7.
On the 2nd image you can see I have added the extra columns (Shaded) but because this could also contain a "7" in the column, it follows the above formula and brings over data in that row.
Using Fluffs formula, I tried to pick just the columns that i required but i am failing big time
I need the formula that FLUFF gave me but only to filter on these columns, D15,F15,H15,J15,L15,N15,P15,R15,T15,V15, W15,X15,Y15

Can anyone help?
TIA
George

Grading.xlsx
ABCDEFGHIJKLMN
1NameClassGenderArt & Design Business Chemistry Dance Design Drama English French Geography Graphics History
2George4AMale426782
3Larry4AMale33333
4Divya4AFemale11187
5Urvesh4AMale54772
6Suzie4BFemale78583
7Beth4BFemale33333
8Tom4BMale35222
9Simone4CFemale22728
10Colin4CMale53553
11Stephanie4CFemale66666
Grading



Grading.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
14NameClassGenderArt & Design A&D GradeBusiness Bus GradeChemistry Chem GradeDance Dance GradeDesign Des GradeDrama Drama GradeEnglish Eng GradeFrench French GradeGeography Geo GradeGraphics Graph GradeHistory Hist Grade
15George4AMale45266745872
16Larry4AMale4335436363
17Divya4AFemale571611845774
18Urvesh4AMale7545747726
19Suzie4BFemale7865845377
20Beth4BFemale6363434337
21Tom4BMale35627244247
22Simone4CFemale226757286
23Colin4CMale653655345576
24Stephanie4CFemale666667
Grading
 
Try
Excel Formula:
=LET(r,SEQUENCE(ROWS(Grading!D2:DH600)),d,INDEX(Grading!D2:DH600,r,SEQUENCE(,27,4,4)),c,COLUMNS(d),s,SEQUENCE(,c+3),f,FILTER(Grading!A2:DH600,MMULT(--(d>=7),SEQUENCE(c,,,0))),IF(f=0,"",f))
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,215,069
Messages
6,122,959
Members
449,096
Latest member
Anshu121

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top