Forum Discussion
Filter combined with Sort by.
Hi,
This function behaves strange. I can not undestand why the result is what is:
=FILTER(SORT.BY(Tabell1[Club];Tabell1[Goals per match];-1);Tabell1[Goals per match]>=$P$10)
Goals per match for Viking is 1,498 for Vålerenga 1,496. So why do they show up in the list? And Odd, who should been in the list is left out.
This function return the righet list:
=FILTER(Tabell1[Club]&" - "&Tabell1[Goals per match];Tabell1[Goals per match]>=P10)
I have nticed it change, if I reorder the source Table. Sort it different. Should tha make any differnce for the Dynamic array functions?
That's since to sorted array is applied criteria from unsorted array, they don't match. As variant that could be
=FILTER(SORTBY(Tabell1[Club],Tabell1[Goals per match],-1),SORTBY(Tabell1[Goals per match],Tabell1[Goals per match],-1)>=$P$10)for the club and
=FILTER(SORTBY(Tabell1[Goals per match],Tabell1[Goals per match],-1),SORTBY(Tabell1[Goals per match],Tabell1[Goals per match],-1)>=$P$10)for the goals.
6 Replies
In Excel one is never restricted to a single solution. Since your output is two columns one could always start by combining the columns you are interested into a two column array by using INDEX or CHOOSE
= IF( {1,0}, Tabell1[Club], Tabell1[Goals per match] )
The array can then be filtered
= FILTER( IF( {1,0}, Tabell1[Club], Tabell1[Goals per match] ),
Tabell1[Goals per match]>=G\M )
and sorted descending by the second column
= SORT( FILTER( IF( {1,0}, Tabell1[Club], Tabell1[Goals per match] ), Tabell1[Goals per match] >= G\M ), 2, -1 )
That's since to sorted array is applied criteria from unsorted array, they don't match. As variant that could be
=FILTER(SORTBY(Tabell1[Club],Tabell1[Goals per match],-1),SORTBY(Tabell1[Goals per match],Tabell1[Goals per match],-1)>=$P$10)for the club and
=FILTER(SORTBY(Tabell1[Goals per match],Tabell1[Goals per match],-1),SORTBY(Tabell1[Goals per match],Tabell1[Goals per match],-1)>=$P$10)for the goals.
Brilliant, Thank you SergeiBaklan
Geir, you are welcome. In addition, I tried to play with modern Q&A in Ideas.
Started from simple question, but was not able to make condition as "is not less than"
Make question more formal, and Ideas returns desired table
Interesting...
