Sortby, Choose, and Filter in one formula

csenor

Board Regular
Joined
Apr 10, 2013
Messages
168
Office Version
  1. 365
Platform
  1. Windows
Hello all. I need some help with a formula. I have a large dataset with 30 columns of data. I need a report with only 5 of those columns. I want to be able to sort by department and then last name. I can't seem to get the SORTBY function to work. Last name is in column one on my report. Department is in column three in my report. Thank you in advance. I've watched some Youtube video, but haven't found one that incorporates filter and sortby yet.

=SORT(FILTER(CHOOSE({1,2,3,4,5},Roster_tbl[Last Name],Roster_tbl[FullName],Roster_tbl[Department],Roster_tbl[Start Date],Roster_tbl[SSN]),(Roster_tbl[Level]=C1)*(Roster_tbl[Status]="Active"),"Level not spelled correctly."),3)
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
No appropriate small table of sample data to test on but try sorting before the filter:

Excel Formula:
=FILTER(SORTBY(CHOOSE({1,2,3,4,5},Roster_tbl[Last name],Roster_tbl[FullName],Roster_tbl[Department],Roster_tbl[Start Date],Roster_tbl[SSN]),Roster_tbl[Department],1,Roster_tbl[Last name],1),(Roster_tbl[Level]=C1)*(Roster_tbl[Status]="Active"),"Level not spelled correctly.")

If that is not it can you provide a smallish set of dummy sample data and expected results (manually entered) with XL2BB

Also, for completeness, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
No appropriate small table of sample data to test on but try sorting before the filter:

Excel Formula:
=FILTER(SORTBY(CHOOSE({1,2,3,4,5},Roster_tbl[Last name],Roster_tbl[FullName],Roster_tbl[Department],Roster_tbl[Start Date],Roster_tbl[SSN]),Roster_tbl[Department],1,Roster_tbl[Last name],1),(Roster_tbl[Level]=C1)*(Roster_tbl[Status]="Active"),"Level not spelled correctly.")

If that is not it can you provide a smallish set of dummy sample data and expected results (manually entered) with XL2BB

Also, for completeness, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Thank you. That worked! I'll try to download the xl2bb add in for the next time.
 
Upvote 0
I was wrong. I thought it worked, however, it isn't taking into consideration the second criteria for the filter. The video I watched indicated that you need to put multiple criteria in parenthesis separated by an asterisk to make it filter for both criteria. As you can tell from the last entry into my data table, I left the level blank. It still grabs that record and puts it into my filtered range.

I downloaded xl2bb and pasted the range below. Hopefully this works.

I want a filtered list of active members from the county level sorted by department and then by last name.

Office 365
Windows 10

Book1
ABCDEFGHIJKLMNOPQ
5
6
7StatusLast NameFull NameDepartmentAddressCityStateZipLevelStart DateStatusDepartmentLast NameLevelStart Date
8ActiveFullerJon FullerCompany 1123 Main St.AnytownNJ08055County1/3/2015ActiveCompany 1DavisCounty3/4/2014
9ActiveDavisMark DavisCompany 1124 Main St.AnytownNJ08055County3/4/2014ActiveCompany 1Doman06/6/2015
10ActiveMathisErin MathisCompany 2125 Main St.AnytownNJ08055Local3/8/2016ActiveCompany 2MatthewsLocal12/23/2017
11RetiredJonesBob JonesCompany 1126 Main St.AnytownNJ08055County2/2/2018
12DeceasedHarperMark HarperCompany 1127 Main St.AnytownNJ08055County1/1/2000
13SeparatedMorrisSharon MorrisCompany 2128 Main St.AnytownNJ08055Local5/25/2010
14ActiveCollinsDevon CollinsCompany 2129 Main St.AnytownNJ08055Local3/18/1988
15ActiveMatthewsSamuel MatthewsCompany 2130 Main St.AnytownNJ08055Local12/23/2017
16ActiveMorrisSharon MorrisCompany 1131 Main St.AnytownNJ08055County8/3/2020
17ActivePorterHarry PorterCompany 3132 Main St.AnytownNJ08055Local4/5/2008
18ActiveDomanJohn DomanCompany 1133 Main St.AnytownNJ080556/6/2015
19
20
21
Sheet1
Cell Formulas
RangeFormula
L8:P10L8=FILTER(SORTBY(CHOOSE({1,2,3,4,5},Data_tbl[Status],Data_tbl[Department],Data_tbl[Last Name],Data_tbl[Level],Data_tbl[Start Date]),Data_tbl[Department],1,Data_tbl[Last Name],1),(Data_tbl[Status]="Active")*(Data_tbl[Level]="County"))
Dynamic array formulas.
 
Upvote 0
The problem is that you are filtering based on the table, but applying that to the sorted array.
Do you have the LET function?
 
Upvote 0
Ok, how about
+Fluff v2.xlsm
ABCDEFGHIJKLMNOP
5
6
7StatusLast NameFull NameDepartmentAddressCityStateZipLevelStart DateStatusDepartmentLast NameLevelStart Date
8ActiveFullerJon FullerCompany 1123 Main St.AnytownNJ8055County03/01/2015ActiveCompany 1DavisCounty04/03/2014
9ActiveDavisMark DavisCompany 1124 Main St.AnytownNJ8055County04/03/2014ActiveCompany 1FullerCounty03/01/2015
10ActiveMathisErin MathisCompany 2125 Main St.AnytownNJ8055Local08/03/2016ActiveCompany 1MorrisCounty03/08/2020
11RetiredJonesBob JonesCompany 1126 Main St.AnytownNJ8055County02/02/2018
12DeceasedHarperMark HarperCompany 1127 Main St.AnytownNJ8055County01/01/2000
13SeparatedMorrisSharon MorrisCompany 2128 Main St.AnytownNJ8055Local25/05/2010
14ActiveCollinsDevon CollinsCompany 2129 Main St.AnytownNJ8055Local18/03/1988
15ActiveMatthewsSamuel MatthewsCompany 2130 Main St.AnytownNJ8055Local23/12/2017
16ActiveMorrisSharon MorrisCompany 1131 Main St.AnytownNJ8055County03/08/2020
17ActivePorterHarry PorterCompany 3132 Main St.AnytownNJ8055Local05/04/2008
18ActiveDomanJohn DomanCompany 1133 Main St.AnytownNJ805506/06/2015
19
Summary
Cell Formulas
RangeFormula
L8:P10L8=LET(Sorted,SORTBY(CHOOSE({1,2,3,4,5},Data_tbl[Status],Data_tbl[Department],Data_tbl[Last Name],Data_tbl[Level],Data_tbl[Start Date]),Data_tbl[Department],1,Data_tbl[Last Name],1),FILTER(Sorted,(INDEX(Sorted,,1)="Active")*(INDEX(Sorted,,4)="County")))
Dynamic array formulas.
 
Upvote 0
Thanks Fluff. That worked, however, when I made up my fake data set, I didn't set up the report correctly. I need Last Name, Full Name, Department, Start Date, and SSN. I don't want to see the word "Active" going down the side of my report. Just once at the top of the page that the formula can reference to change between local or county depending on what I need to see. Since you added the Index function, I'm not sure how to address that.

I'm sorry for the confusion.

Book1
CDEFGHIJKLMNOP
3
4Level:local
5
6
7Full NameDepartmentAddressCityStateSSNLevelStart DateLast NameFullNameDepartmentStart DateSSN
8Jon FullerCompany 1123 Main St.AnytownNJ321-93-2594County1/3/2015#VALUE!
9Mark DavisCompany 1124 Main St.AnytownNJ204-06-8429County3/4/2014
10Erin MathisCompany 2125 Main St.AnytownNJ511-22-0179Local3/8/2016
11Bob JonesCompany 1126 Main St.AnytownNJ882-94-1949County2/2/2018
12Mark HarperCompany 1127 Main St.AnytownNJ612-95-5430County1/1/2000
13Sharon MorrisCompany 2128 Main St.AnytownNJ281-76-5557Local5/25/2010
14Devon CollinsCompany 2129 Main St.AnytownNJ124-20-8178Local3/18/1988
15Samuel MatthewsCompany 2130 Main St.AnytownNJ641-34-9611Local12/23/2017
16Sharon MorrisCompany 1131 Main St.AnytownNJ863-34-6165County8/3/2020
17Harry PorterCompany 3132 Main St.AnytownNJ182-02-6573Local4/5/2008
18John DomanCompany 1133 Main St.AnytownNJ962-48-3635County6/6/2015
19
20
21
Sheet1
Cell Formulas
RangeFormula
L8L8=LET(Sorted,SORTBY(CHOOSE({1,2,3,4,5},Data_tbl[Last Name],Data_tbl[Full Name],Data_tbl[Department],Data_tbl[Start Date],Data_tbl[SSN]),Data_tbl[Department],1,Data_tbl[Last Name],1),FILTER(Sorted,(INDEX(Sorted,,Data_tbl[Status])="active")*(INDEX(Sorted,,Data_tbl[Level])=N4)))
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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