filter and sort function (custom sort list order)

[ExcelNewbie2020]

Gurus,

I was able to use the filter function, but I can't apply the sorting based on my custom sort list order. Please see attached

Thank you very much,

 

[bebo021999]

How about VBA solution with a button, with a click, result will be in other sheet?
If its OK, try to upload mini sheet via XL2BB.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Peter_SSs]

Can you give us the data with XL2BB so we can copy to test with?

 

[ExcelNewbie2020]

Can you give us the data with XL2BB so we can copy to test with?

custom sor list order.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	NAME	EMP	DEPARTMENT	07-31-2022	08-01-2022	08-02-2022	08-03-2022
3	Tom	201	cc	2	1	2	1		custom sort list order
4	Jack	202	db	3		3			bb
5	Suzie	203	aa		2		1		bc
6	Rajesh	204	ab		4				aa
7	Kevin	205	bb	2	2	2	2		ab
8	Belinda	206	bc	3	3	3	3		cc
9	Sally	207	bc						db
10	David	208	aa	1	2
11	Joe	209	cc		7	2	1
12	Jim	210	db	1	5	3
13
14			SHOULD BE RESULT
15			NAME	EMP	DEPARTMENT		Criteria	Today (08/1/2022)
16			Kevin	205	bb			>1
17			Belinda	206	bc		Skip empty cells
18			Suzie	203	aa		sort by custom list order
19			David	208	aa
20			Rajesh	204	ab		filter the name, emp and department which contains values greater than 1 and that matching the current date
21			Joe	209	cc		The result should be sorted based on the custom sort list order
22			Jim	210	db
Sheet1 (2)

Thank you so much

 

[ExcelNewbie2020]

How about VBA solution with a button, with a click, result will be in other sheet?
If its OK, try to upload mini sheet via XL2BB.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

thank you for the suggestion.. will try that if there is no way we can do it via excel functions..

 

[Fluff]

How about

=LET(a,INDEX(D2:G11,,MATCH(TODAY(),D1:G1,0)),b,FILTER(A2:C11,a>1),SORTBY(b,MATCH(INDEX(b,,3),I3:I8,0)))

 

[ExcelNewbie2020]

Awesome, thank you so much bro...

 

[Peter_SSs]

Since 'a' is only used once in subsequent calculations the formula could be shortened/simplified(?) ever-so-slightly by removing it.

=LET(b,FILTER(A2:C11,INDEX(D2:G11,,MATCH(TODAY(),D1:G1,0))>1),SORTBY(b,MATCH(INDEX(b,,3),I3:I8,0)))

 

[Fluff]

Glad we could help & thanks for the feedback.