Sortby, Choose, and Filter in one formula

csenor

Board Regular
Joined
Apr 10, 2013
Messages
159
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)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
Another option
+Fluff v2.xlsm
ABCDEFGHIJKLMNOPQR
1
2
3
4Level:local
5
6
7StatusLast NameFull NameDepartmentAddressCityStateSSNLevelStart DateLast NameFull NameDepartmentStart DateSSNaddress
8ActiveFullerJon FullerCompany 1123 Main St.AnytownNJ321932594County03/01/2015MatthewsSamuel MatthewsCompany 123/12/2017641349611130 Main St.
9ActiveDavisMark DavisCompany 1124 Main St.AnytownNJ204068429County04/03/2014CollinsDevon CollinsCompany 218/03/1988124208178129 Main St.
10ActiveMathisErin MathisCompany 2125 Main St.AnytownNJ511220179Local08/03/2016MathisErin MathisCompany 208/03/2016511220179125 Main St.
11RetiredJonesBob JonesCompany 1126 Main St.AnytownNJ882941949County02/02/2018PorterHarry PorterCompany 305/04/2008182026573132 Main St.
12DeceasedHarperMark HarperCompany 1127 Main St.AnytownNJ612955430County01/01/2000
13SeparatedMorrisSharon MorrisCompany 2128 Main St.AnytownNJ281765557Local25/05/2010
14ActiveCollinsDevon CollinsCompany 2129 Main St.AnytownNJ124208178Local18/03/1988
15ActiveMatthewsSamuel MatthewsCompany 1130 Main St.AnytownNJ641349611Local23/12/2017
16ActiveMorrisSharon MorrisCompany 1131 Main St.AnytownNJ863346165County03/08/2020
17ActivePorterHarry PorterCompany 3132 Main St.AnytownNJ182026573Local05/04/2008
18ActiveDomanJohn DomanCompany 1133 Main St.AnytownNJ962483635County06/06/2015
19
20
Summary
Cell Formulas
RangeFormula
L8:Q11L8=LET(Hdr,L7:Q7,FltrCol,FILTER(Data_tbl[#All],COUNTIF(Hdr,Data_tbl[#Headers])),SortCol,SORTBY(FltrCol,MATCH(INDEX(FltrCol,1,),Hdr,0)),FltRw,FILTER(SortCol,(Data_tbl[[#All],[Status]]="Active")*(Data_tbl[[#All],[Level]]=N4)),SORTBY(FltRw,INDEX(FltRw,,3),1,INDEX(FltRw,,1),1))
Dynamic array formulas.


This will match the headers in L7:Q7 & sort/filter accordingly & you only have to change the header range in onespot if you want to show more/less columns
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

csenor

Board Regular
Joined
Apr 10, 2013
Messages
159
Office Version
  1. 365
Platform
  1. Windows
Another option
+Fluff v2.xlsm
ABCDEFGHIJKLMNOPQR
1
2
3
4Level:local
5
6
7StatusLast NameFull NameDepartmentAddressCityStateSSNLevelStart DateLast NameFull NameDepartmentStart DateSSNaddress
8ActiveFullerJon FullerCompany 1123 Main St.AnytownNJ321932594County03/01/2015MatthewsSamuel MatthewsCompany 123/12/2017641349611130 Main St.
9ActiveDavisMark DavisCompany 1124 Main St.AnytownNJ204068429County04/03/2014CollinsDevon CollinsCompany 218/03/1988124208178129 Main St.
10ActiveMathisErin MathisCompany 2125 Main St.AnytownNJ511220179Local08/03/2016MathisErin MathisCompany 208/03/2016511220179125 Main St.
11RetiredJonesBob JonesCompany 1126 Main St.AnytownNJ882941949County02/02/2018PorterHarry PorterCompany 305/04/2008182026573132 Main St.
12DeceasedHarperMark HarperCompany 1127 Main St.AnytownNJ612955430County01/01/2000
13SeparatedMorrisSharon MorrisCompany 2128 Main St.AnytownNJ281765557Local25/05/2010
14ActiveCollinsDevon CollinsCompany 2129 Main St.AnytownNJ124208178Local18/03/1988
15ActiveMatthewsSamuel MatthewsCompany 1130 Main St.AnytownNJ641349611Local23/12/2017
16ActiveMorrisSharon MorrisCompany 1131 Main St.AnytownNJ863346165County03/08/2020
17ActivePorterHarry PorterCompany 3132 Main St.AnytownNJ182026573Local05/04/2008
18ActiveDomanJohn DomanCompany 1133 Main St.AnytownNJ962483635County06/06/2015
19
20
Summary
Cell Formulas
RangeFormula
L8:Q11L8=LET(Hdr,L7:Q7,FltrCol,FILTER(Data_tbl[#All],COUNTIF(Hdr,Data_tbl[#Headers])),SortCol,SORTBY(FltrCol,MATCH(INDEX(FltrCol,1,),Hdr,0)),FltRw,FILTER(SortCol,(Data_tbl[[#All],[Status]]="Active")*(Data_tbl[[#All],[Level]]=N4)),SORTBY(FltRw,INDEX(FltRw,,3),1,INDEX(FltRw,,1),1))
Dynamic array formulas.


This will match the headers in L7:Q7 & sort/filter accordingly & you only have to change the header range in onespot if you want to show more/less columns
Thank you.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
My pleasure.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,588
Messages
5,597,049
Members
414,117
Latest member
marblepoint

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
Top