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
53,071
Office Version
  1. 365
Platform
  1. Windows
Could you please repost the data including columns A & B. ;)
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

csenor

Board Regular
Joined
Apr 10, 2013
Messages
159
Office Version
  1. 365
Platform
  1. Windows
I'm so sorry. Thank you for being patient with me.

Book1
ABCDEFGHIJKLMNOP
3
4Level:local
5
6
7StatusLast NameFull NameDepartmentAddressCityStateSSNLevelStart DateLast NameFullNameDepartmentStart DateSSN
8ActiveFullerJon FullerCompany 1123 Main St.AnytownNJ321-93-2594County1/3/2015#VALUE!
9ActiveDavisMark DavisCompany 1124 Main St.AnytownNJ204-06-8429County3/4/2014
10ActiveMathisErin MathisCompany 2125 Main St.AnytownNJ511-22-0179Local3/8/2016
11RetiredJonesBob JonesCompany 1126 Main St.AnytownNJ882-94-1949County2/2/2018
12DeceasedHarperMark HarperCompany 1127 Main St.AnytownNJ612-95-5430County1/1/2000
13SeparatedMorrisSharon MorrisCompany 2128 Main St.AnytownNJ281-76-5557Local5/25/2010
14ActiveCollinsDevon CollinsCompany 2129 Main St.AnytownNJ124-20-8178Local3/18/1988
15ActiveMatthewsSamuel MatthewsCompany 2130 Main St.AnytownNJ641-34-9611Local12/23/2017
16ActiveMorrisSharon MorrisCompany 1131 Main St.AnytownNJ863-34-6165County8/3/2020
17ActivePorterHarry PorterCompany 3132 Main St.AnytownNJ182-02-6573Local4/5/2008
18ActiveDomanJohn 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)))
 

csenor

Board Regular
Joined
Apr 10, 2013
Messages
159
Office Version
  1. 365
Platform
  1. Windows
I'm so sorry. Thank you for being patient with me.

Book1
ABCDEFGHIJKLMNOP
3
4Level:local
5
6
7StatusLast NameFull NameDepartmentAddressCityStateSSNLevelStart DateLast NameFullNameDepartmentStart DateSSN
8ActiveFullerJon FullerCompany 1123 Main St.AnytownNJ321-93-2594County1/3/2015#VALUE!
9ActiveDavisMark DavisCompany 1124 Main St.AnytownNJ204-06-8429County3/4/2014
10ActiveMathisErin MathisCompany 2125 Main St.AnytownNJ511-22-0179Local3/8/2016
11RetiredJonesBob JonesCompany 1126 Main St.AnytownNJ882-94-1949County2/2/2018
12DeceasedHarperMark HarperCompany 1127 Main St.AnytownNJ612-95-5430County1/1/2000
13SeparatedMorrisSharon MorrisCompany 2128 Main St.AnytownNJ281-76-5557Local5/25/2010
14ActiveCollinsDevon CollinsCompany 2129 Main St.AnytownNJ124-20-8178Local3/18/1988
15ActiveMatthewsSamuel MatthewsCompany 2130 Main St.AnytownNJ641-34-9611Local12/23/2017
16ActiveMorrisSharon MorrisCompany 1131 Main St.AnytownNJ863-34-6165County8/3/2020
17ActivePorterHarry PorterCompany 3132 Main St.AnytownNJ182-02-6573Local4/5/2008
18ActiveDomanJohn 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)))
In my real dataset, my status column is column B and Level is AB
 

csenor

Board Regular
Joined
Apr 10, 2013
Messages
159
Office Version
  1. 365
Platform
  1. Windows
In my real dataset, my status column is column B and Level is AB
By me using Sorted in my Index function array, I get a value error when I try to specify the real column number for Status and Level, because they aren't one of the columns in my Choose formula.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The main problem is that you want to reorder the columns.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
+Fluff v2.xlsm
ABCDEFGHIJKLMNOP
1
2
3
4Level:local
5
6
7StatusLast NameFull NameDepartmentAddressCityStateSSNLevelStart DateLast NameFull NameDepartmentStart DateSSN
8ActiveFullerJon FullerCompany 1123 Main St.AnytownNJ321932594County03/01/2015CollinsDevon CollinsCompany 218/03/1988124208178
9ActiveDavisMark DavisCompany 1124 Main St.AnytownNJ204068429County04/03/2014MathisErin MathisCompany 208/03/2016511220179
10ActiveMathisErin MathisCompany 2125 Main St.AnytownNJ511220179Local08/03/2016MatthewsSamuel MatthewsCompany 223/12/2017641349611
11RetiredJonesBob JonesCompany 1126 Main St.AnytownNJ882941949County02/02/2018PorterHarry PorterCompany 305/04/2008182026573
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 2130 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
Summary
Cell Formulas
RangeFormula
L8:P11L8=LET(FltrRw,FILTER(Data_tbl,(Data_tbl[Status]="Active")*(Data_tbl[Level]=N4)),FltrCol,FILTER(FltrRw,COUNTIFS(L7:P7,Data_tbl[#Headers])),SortRw,SORTBY(FltrCol,INDEX(FltrCol,,3),1,INDEX(FltrCol,,1),1),SORTBY(SortRw,{1,2,3,5,4},1))
Dynamic array formulas.
 
Last edited:
Solution

csenor

Board Regular
Joined
Apr 10, 2013
Messages
159
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

When I paste your formula in, I get a value error. Nevermind. I didn't have a space in Full Name.
 
Last edited:

csenor

Board Regular
Joined
Apr 10, 2013
Messages
159
Office Version
  1. 365
Platform
  1. Windows
When I paste your formula in, I get a value error. Nevermind. I didn't have a space in Full Name.
Thank you so much! I would have never came up with that formula. Your help is much appreciated.
 

KevCarter

Board Regular
Joined
Dec 7, 2013
Messages
145
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Ok, how about
+Fluff v2.xlsm
ABCDEFGHIJKLMNOP
1
2
3
4Level:local
5
6
7StatusLast NameFull NameDepartmentAddressCityStateSSNLevelStart DateLast NameFull NameDepartmentStart DateSSN
8ActiveFullerJon FullerCompany 1123 Main St.AnytownNJ321932594County03/01/2015CollinsDevon CollinsCompany 218/03/1988124208178
9ActiveDavisMark DavisCompany 1124 Main St.AnytownNJ204068429County04/03/2014MathisErin MathisCompany 208/03/2016511220179
10ActiveMathisErin MathisCompany 2125 Main St.AnytownNJ511220179Local08/03/2016MatthewsSamuel MatthewsCompany 223/12/2017641349611
11RetiredJonesBob JonesCompany 1126 Main St.AnytownNJ882941949County02/02/2018PorterHarry PorterCompany 305/04/2008182026573
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 2130 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
Summary
Cell Formulas
RangeFormula
L8:P11L8=LET(FltrRw,FILTER(Data_tbl,(Data_tbl[Status]="Active")*(Data_tbl[Level]=N4)),FltrCol,FILTER(FltrRw,COUNTIFS(L7:P7,Data_tbl[#Headers])),SortRw,SORTBY(FltrCol,INDEX(FltrCol,,3),1,INDEX(FltrCol,,1),1),SORTBY(SortRw,{1,2,3,5,4},1))
Dynamic array formulas.

Mr. Fluff, I learn so much and so many techniques I would never have thought of by using your posts as a tutorial. You are truly amazing. I just wanted to add my thanks as well...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,832
Messages
5,598,361
Members
414,233
Latest member
WolverineNurse

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