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)
 
Could you please repost the data including columns A & B. ;)
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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)))
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
The main problem is that you want to reorder the columns.
 
Upvote 0
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:
Upvote 0
Solution
When I paste your formula in, I get a value error. Nevermind. I didn't have a space in Full Name.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,479
Messages
6,113,897
Members
448,530
Latest member
yatong2008

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