SORTBY Function not working as expected

[zero269]

Hello,

I'm trying to use the FILTER Function along with the SORTBY function to sort by two different columns. However, it's not sorting the same way it does when sorting manually in a Table.
Here's what the data looks like when sorting by Title first and then by Test Date within a Table:
We can see that the Titles are in ascending order, and then the Test Dates. Titles with no Test Date remain in ascending order below the ones with a Test Date.

Here's what it looks like when I use SORTBY Title then Test Date using the Filter Function:
This method seems to disregard the 2nd Sort Order to the Test Date…

Here's what my SAMPLE DATA Looks like:

VBA Testing.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Quiz	Title	Book Level	Word Count	Test Date	Book Status	Book No.		On Hand		Test Date	Quiz	Title
2	122822	Mysterious Island (Saddleback)	3.5	6,146	6-May	available			available			513611	30,000 Stitches: Story of the National 9/11 Flag
3	188059	Who Hatches the Egg? All About Eggs	3.5	935	6-May	available			library		9-May	122820	A Tale of Two Cities (Saddleback)
4	191363	Exploring Pluto and Other Dwarf Planets	3.8	463	7-May	available			on hold			193924	Apollo's First Moon Landing
5	195765	When God Made Light	3.6	538	7-May	available					8-May	7172	Berenstain Bears to the Rescue
6	7172	Berenstain Bears to the Rescue	3.7	582	8-May	available						122187	Best of Poe (Saddleback)
7	501222	United States Navy	3.7	250	8-May	available					9-May	52030	Box Turtle at Silver Pond Lane
8	122820	A Tale of Two Cities (Saddleback)	3.6	5,171	9-May	available						59784	Corduroy's Christmas Surprise
9	52030	Box Turtle at Silver Pond Lane	3.8	873	9-May	available						59785	Corduroy's Easter Party
10	191357	Exploring Comets and Asteroids	3.9	410	10-May	available					10-May	191357	Exploring Comets and Asteroids
11	509378	Lara Croft: Tomb Raider Hero	3.9	342	10-May	available					7-May	191363	Exploring Pluto and Other Dwarf Planets
12	513611	30,000 Stitches: Story of the National 9/11 Flag	3.9	596		on hold						180821	From Tadpole to Frog
13	193924	Apollo's First Moon Landing	3.8	1,941		available						141878	God Gave Us the World
14	122187	Best of Poe (Saddleback)	3.9	5,212		available						500157	Harriet Tubman: Freedom Fighter
15	59784	Corduroy's Christmas Surprise	3.3	993		available						122803	Hunchback of Notre Dame (Saddleback)
16	59785	Corduroy's Easter Party	3.0	857		available						5519	Jumanji
17	180821	From Tadpole to Frog	2.5	240		available					10-May	509378	Lara Croft: Tomb Raider Hero
18	141878	God Gave Us the World	2.6	870		available						122805	Last of the Mohicans (Saddleback)
19	500157	Harriet Tubman: Freedom Fighter	3.8	667		on hold						192177	Lion and the Mouse
20	122803	Hunchback of Notre Dame (Saddleback)	3.6	5,479		available						122806	Man in the Iron Mask (Saddleback)
21	105146	It's the Easter Beagle, Charlie Brown	3.5	1,527		on order						59949	Mount Rushmore
22	5519	Jumanji	3.9	1,800		on hold					6-May	122822	Mysterious Island (Saddleback)
23	122805	Last of the Mohicans (Saddleback)	3.5	6,112		available						186903	Paul Revere's Ride
24	192177	Lion and the Mouse	3.3	600		available						186904	Pilgrims' Voyage to America
25	122806	Man in the Iron Mask (Saddleback)	3.2	5,674		available						122824	Red Badge of Courage (Saddleback)
26	59949	Mount Rushmore	3.7	636		available						111465	Soccer
27	36459	New Kid at School	3.3	10,043		on order	1					16860	Town Mouse and the Country Mouse
28	186903	Paul Revere's Ride	3.8	1,791		available					8-May	501222	United States Navy
29	186904	Pilgrims' Voyage to America	3.7	1,918		available						122829	War of the Worlds (Saddleback)
30	122824	Red Badge of Courage (Saddleback)	2.7	3,758		available						111746	Welcome to Narnia
31	111465	Soccer	3.3	444		available					7-May	195765	When God Made Light
32	16860	Town Mouse and the Country Mouse	3.9	1,059		available					6-May	188059	Who Hatches the Egg? All About Eggs
33	122829	War of the Worlds (Saddleback)	3.8	3,740		available						64293	Zathura: A Space Adventure
34	111746	Welcome to Narnia	3.3	467		available
35	64293	Zathura: A Space Adventure	3.8	2,273		on hold
SortBy

Cell Formulas
Range	Formula
K2:M33	K2	=LET( x,FILTER(CHOOSECOLS(t_Students,5,1,2),COUNTIF(l_OnHand,t_Students[Book Status])), SORTBY(x,INDEX(x,,3),1,INDEX(x,,1),1))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
l_OnHand	=t_BookStatus[On Hand]	K2:M33

Any help would be greatly appreciated.
Thank you…

 

[myall_blues]

Your data in the first picture are NOT sorted by Title. The SORTBY is doing it correctly.
If you use Auto-filter on data, you can only sort one column at a time - in your case the sort order is by Test Date as indicated by the arrow on the dropdown indicator.
If it were sorted by Title the 'A' titles would come before the 'B' titles and so on, as they are in the SORTBY result.

If you want to sort by more than one field then you need to use Sort from the ribbon, not autofilter sorting, like so:

If you do this you will get the same result as the SORTBY. When you sort by 2 or more fields, the second and subsequent fields are only used as tiebreakers. Thus in your case the date only becomes relevant to the sort if you have two identical titles.

 

[Cubist]

Try:

=CHOOSECOLS(SORTBY(A2:E35,E2:E35,1,B2:B35,1),1,2,5)

 

[Fluff]

Another option

=LET(x,FILTER(CHOOSECOLS(t_Students,5,1,2),COUNTIF(l_OnHand,t_Students[Book Status])),SORT(x,{1,3}))

 

[zero269]

Another option

=LET(x,FILTER(CHOOSECOLS(t_Students,5,1,2),COUNTIF(l_OnHand,t_Students[Book Status])),SORT(x,{1,3}))

Hello Fluff,

Thanks for your help on this! I was able to plug your formula in 'as is' and it produced the filtered/sorted data perfectly to match what I was doing with the Table data.

VBA Testing.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	SAMPLE DATA										FLUFF
2	Quiz	Title	Book Level	Word Count	Test Date	Book Status	Book No.		On Hand		Test Date	Quiz	Title
3	122822	Mysterious Island (Saddleback)	3.5	6,146	6-May	available			available		6-May	122822	Mysterious Island (Saddleback)
4	188059	Who Hatches the Egg? All About Eggs	3.5	935	6-May	available			library		6-May	188059	Who Hatches the Egg? All About Eggs
5	191363	Exploring Pluto and Other Dwarf Planets	3.8	463	7-May	available			on hold		7-May	191363	Exploring Pluto and Other Dwarf Planets
6	195765	When God Made Light	3.6	538	7-May	available					7-May	195765	When God Made Light
7	7172	Berenstain Bears to the Rescue	3.7	582	8-May	available					8-May	7172	Berenstain Bears to the Rescue
8	501222	United States Navy	3.7	250	8-May	available					8-May	501222	United States Navy
9	122820	A Tale of Two Cities (Saddleback)	3.6	5,171	9-May	available					9-May	122820	A Tale of Two Cities (Saddleback)
10	52030	Box Turtle at Silver Pond Lane	3.8	873	9-May	available					9-May	52030	Box Turtle at Silver Pond Lane
11	191357	Exploring Comets and Asteroids	3.9	410	10-May	available					10-May	191357	Exploring Comets and Asteroids
12	509378	Lara Croft: Tomb Raider Hero	3.9	342	10-May	available					10-May	509378	Lara Croft: Tomb Raider Hero
13	513611	30,000 Stitches: Story of the National 9/11 Flag	3.9	596		on hold						513611	30,000 Stitches: Story of the National 9/11 Flag
14	193924	Apollo's First Moon Landing	3.8	1,941		available						193924	Apollo's First Moon Landing
15	122187	Best of Poe (Saddleback)	3.9	5,212		available						122187	Best of Poe (Saddleback)
16	59784	Corduroy's Christmas Surprise	3.3	993		home						59785	Corduroy's Easter Party
17	59785	Corduroy's Easter Party	3.0	857		available						180821	From Tadpole to Frog
18	180821	From Tadpole to Frog	2.5	240		available						141878	God Gave Us the World
19	141878	God Gave Us the World	2.6	870		available						500157	Harriet Tubman: Freedom Fighter
20	500157	Harriet Tubman: Freedom Fighter	3.8	667		on hold						5519	Jumanji
21	122803	Hunchback of Notre Dame (Saddleback)	3.6	5,479		home						122805	Last of the Mohicans (Saddleback)
22	105146	It's the Easter Beagle, Charlie Brown	3.5	1,527		on order						192177	Lion and the Mouse
23	5519	Jumanji	3.9	1,800		on hold						122806	Man in the Iron Mask (Saddleback)
24	122805	Last of the Mohicans (Saddleback)	3.5	6,112		available						59949	Mount Rushmore
25	192177	Lion and the Mouse	3.3	600		available						186904	Pilgrims' Voyage to America
26	122806	Man in the Iron Mask (Saddleback)	3.2	5,674		available						122824	Red Badge of Courage (Saddleback)
27	59949	Mount Rushmore	3.7	636		available						111465	Soccer
28	36459	New Kid at School	3.3	10,043		on order	1					16860	Town Mouse and the Country Mouse
29	186903	Paul Revere's Ride	3.8	1,791		home						111746	Welcome to Narnia
30	186904	Pilgrims' Voyage to America	3.7	1,918		available						64293	Zathura: A Space Adventure
31	122824	Red Badge of Courage (Saddleback)	2.7	3,758		available
32	111465	Soccer	3.3	444		available
33	16860	Town Mouse and the Country Mouse	3.9	1,059		available
34	122829	War of the Worlds (Saddleback)	3.8	3,740		home
35	111746	Welcome to Narnia	3.3	467		available
36	64293	Zathura: A Space Adventure	3.8	2,273		on hold
37
SortBy

Cell Formulas
Range	Formula
K3:M30	K3	=LET(x,FILTER(CHOOSECOLS(t_Students,5,1,2),COUNTIF(l_OnHand,t_Students[Book Status])),SORT(x,{1,3}))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
l_OnHand	=t_OnHand[On Hand]	K3:M30

I didn't realize you could use more than one criteria with the SORT function in that way (as an array?): SORT(x,{1,3})
I saw examples of using INDEX and it seemed to make sense to me… but wasn't sure why it failed to produce the results I was expecting.

I have another one I'm working on where the Word Count needs to be sorted DESCENDING and Test Date ASCENDING (default), so I tested it using your method: SORT(x,{1,3},{-1,1}). I ran my test by reversing the dates and keeping the titles in order:

VBA Testing.xlsm
	K	L	M
2	Test Date	Quiz	Title
3	10-May	191357	Exploring Comets and Asteroids
4	10-May	509378	Lara Croft: Tomb Raider Hero
5	9-May	122820	A Tale of Two Cities (Saddleback)
6	9-May	52030	Box Turtle at Silver Pond Lane
7	8-May	7172	Berenstain Bears to the Rescue
8	8-May	501222	United States Navy
9	7-May	191363	Exploring Pluto and Other Dwarf Planets
10	7-May	195765	When God Made Light
11	6-May	141878	God Gave Us the World
12	6-May	122806	Man in the Iron Mask (Saddleback)
13	6-May	122822	Mysterious Island (Saddleback)
14	6-May	186904	Pilgrims' Voyage to America
15	6-May	188059	Who Hatches the Egg? All About Eggs
16		513611	30,000 Stitches: Story of the National 9/11 Flag
17		193924	Apollo's First Moon Landing
18		122187	Best of Poe (Saddleback)
SortBy

Cell Formulas
Range	Formula
K3:M30	K3	=LET(x,FILTER(CHOOSECOLS(t_Students,5,1,2),COUNTIF(l_OnHand,t_Students[Book Status])),SORT(x,{1,3},{-1,1}))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
l_OnHand	=t_OnHand[On Hand]	K3:M18

This worksperfectly and is a great improvement to all of my earlier failed experiments…

Thanks again, Fluff for all your help.

 

[zero269]

Try:

=CHOOSECOLS(SORTBY(A2:E35,E2:E35,1,B2:B35,1),1,2,5)

Hi Cubist,

Thanks for your suggestion. Although it's missing the FILTER function, it certainly sorts the data correctly.
I also had to limit my filtering to my Named Range (l_OnHand) and the Column Order for the output would be (5,1,2); no big deal there as it's a simple fix.

I'm trying to use the FILTER Function along with the SORTBY function to sort by two different columns.

Thanks again; I appreciate you taking the time to offer me help.

 

[zero269]

Your data in the first picture are NOT sorted by Title.

Hi myall_blues,

I was manaully sorting the Table by Title first then by Date. Excel only shows the last column sorted... which may imply I was only sorting by Date. The sample data shows that each date has the titles in ascending order, and Titles without dates are allso in ascending order...

Currently I'm using a macro to build out my print log in a Table, but I'm trying to switch over to a single formula using Filtering/Sorting.. which will later be activated by a macro to choose betweento types of Print Logs...

Thanks again and best regards...

 

[Fluff]

Glad we could help & thanks for the feedback.