Sortby, Choose, and Filter in one formula

[csenor]

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]

Another option

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4													Level:	local
5
6
7	Status	Last Name	Full Name	Department	Address	City	State	SSN	Level	Start Date		Last Name	Full Name	Department	Start Date	SSN	address
8	Active	Fuller	Jon Fuller	Company 1	123 Main St.	Anytown	NJ	321932594	County	03/01/2015		Matthews	Samuel Matthews	Company 1	23/12/2017	641349611	130 Main St.
9	Active	Davis	Mark Davis	Company 1	124 Main St.	Anytown	NJ	204068429	County	04/03/2014		Collins	Devon Collins	Company 2	18/03/1988	124208178	129 Main St.
10	Active	Mathis	Erin Mathis	Company 2	125 Main St.	Anytown	NJ	511220179	Local	08/03/2016		Mathis	Erin Mathis	Company 2	08/03/2016	511220179	125 Main St.
11	Retired	Jones	Bob Jones	Company 1	126 Main St.	Anytown	NJ	882941949	County	02/02/2018		Porter	Harry Porter	Company 3	05/04/2008	182026573	132 Main St.
12	Deceased	Harper	Mark Harper	Company 1	127 Main St.	Anytown	NJ	612955430	County	01/01/2000
13	Separated	Morris	Sharon Morris	Company 2	128 Main St.	Anytown	NJ	281765557	Local	25/05/2010
14	Active	Collins	Devon Collins	Company 2	129 Main St.	Anytown	NJ	124208178	Local	18/03/1988
15	Active	Matthews	Samuel Matthews	Company 1	130 Main St.	Anytown	NJ	641349611	Local	23/12/2017
16	Active	Morris	Sharon Morris	Company 1	131 Main St.	Anytown	NJ	863346165	County	03/08/2020
17	Active	Porter	Harry Porter	Company 3	132 Main St.	Anytown	NJ	182026573	Local	05/04/2008
18	Active	Doman	John Doman	Company 1	133 Main St.	Anytown	NJ	962483635	County	06/06/2015
19
20
Summary

Cell Formulas
Range	Formula
L8:Q11	L8	=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

 

[csenor]

Another option

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4													Level:	local
5
6
7	Status	Last Name	Full Name	Department	Address	City	State	SSN	Level	Start Date		Last Name	Full Name	Department	Start Date	SSN	address
8	Active	Fuller	Jon Fuller	Company 1	123 Main St.	Anytown	NJ	321932594	County	03/01/2015		Matthews	Samuel Matthews	Company 1	23/12/2017	641349611	130 Main St.
9	Active	Davis	Mark Davis	Company 1	124 Main St.	Anytown	NJ	204068429	County	04/03/2014		Collins	Devon Collins	Company 2	18/03/1988	124208178	129 Main St.
10	Active	Mathis	Erin Mathis	Company 2	125 Main St.	Anytown	NJ	511220179	Local	08/03/2016		Mathis	Erin Mathis	Company 2	08/03/2016	511220179	125 Main St.
11	Retired	Jones	Bob Jones	Company 1	126 Main St.	Anytown	NJ	882941949	County	02/02/2018		Porter	Harry Porter	Company 3	05/04/2008	182026573	132 Main St.
12	Deceased	Harper	Mark Harper	Company 1	127 Main St.	Anytown	NJ	612955430	County	01/01/2000
13	Separated	Morris	Sharon Morris	Company 2	128 Main St.	Anytown	NJ	281765557	Local	25/05/2010
14	Active	Collins	Devon Collins	Company 2	129 Main St.	Anytown	NJ	124208178	Local	18/03/1988
15	Active	Matthews	Samuel Matthews	Company 1	130 Main St.	Anytown	NJ	641349611	Local	23/12/2017
16	Active	Morris	Sharon Morris	Company 1	131 Main St.	Anytown	NJ	863346165	County	03/08/2020
17	Active	Porter	Harry Porter	Company 3	132 Main St.	Anytown	NJ	182026573	Local	05/04/2008
18	Active	Doman	John Doman	Company 1	133 Main St.	Anytown	NJ	962483635	County	06/06/2015
19
20
Summary

Cell Formulas
Range	Formula
L8:Q11	L8	=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]

My pleasure.