Filter combined with Sort by.

[Geir Hogstad]

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?

[#2 · Accepted solution]

@Geir Hogstad 

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.

[#3]

Brilliant, Thank you @Sergei Baklan 

[#4]

@Geir Hogstad 

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...

[#5]

@Geir Hogstad 

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 )

[#6]

Hi, @Sergei Baklan first time I have seen Idea in use. Interesting and cool. I do not have that in my Exce-version yet. I have the insider version, but in Norwegian.

Geir

[#7]

Thank you @Peter Bartholomew It works perfect.

- Geir