filter, sortby then exclude specific rows that is included in the filter criteria

ExcelNewbie2020

Active Member
Joined
Dec 3, 2020
Messages
295
Office Version
  1. 365
Platform
  1. Windows
Would it be possible to exclude rows from a filtered list in which that said rows are included from the filtered criteria
i have a current formula that will filter >5 # of days (courtesy of sir @Fluff ). However, i have a separate report that i need to exclude 1 name (Welles) from the list.

testing.xlsx
ABCDEFGHIJKLMNOPQRST
1Nameno.sectiondeptqtyCurrent formulaexpected result
2Lara1ZAZ5SORTBYNameno.dept# of days (updated everyday)Nameno.dept# of days (updated everyday)
3Gayle2ABA10ZGayle2A10Gayle2A10
4Ponting3CDCAKipling7X6Kipling7X6
5Welles4DED20XConnery10B32Connery10B32
6Perot5BCB5BWelles4D20Porter9D12
7Murdoch6EFE4CPorter9D12
8Kipling7XYX6D
9Coolidge8TUTT
10Porter9DED12E
11Connery10BCB32
Sheet11
Cell Formulas
RangeFormula
I3:L7I3=LET(f,FILTER(CHOOSECOLS(A2:E100,1,2,4,5),E2:E100>5),SORTBY(f,MATCH(INDEX(f,,3),G3:G10,0)))
Dynamic array formulas.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Maybe:
Excel Formula:
=LET(f,FILTER(CHOOSECOLS(A2:E100,1,2,4,5),(E2:E100>5)*(A2:A100<>"Welles")),SORTBY(f,MATCH(INDEX(f,,3),G3:G10,0)))
 
Upvote 0
Solution

Forum statistics

Threads
1,215,701
Messages
6,126,308
Members
449,308
Latest member
VerifiedBleachersAttendee

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