Forum Discussion
Sortby
- Pass the array through a FILTER function:
=SORTBY(FILTER(D2:D76,D2:D76<>""),FILTER(RANDARRAY(LARGE(B2:B76,1)),D2:D76<>""))
Firstly, I do not understand the role of LARGE(B2:B76,1) in your formula
=SORTBY(D2:D76,RANDARRAY(LARGE(B2:B76,1)))
The range D2:D76 has 75 cells so unless B2:B76 has 75 as its maximum value the formula will fail; i.e.
= SORTBY( numbers, RANDARRAY(75) )
will do the same job.
The next thing I don't understand is why you want to avoid FILTER at the same time as your stated objective is to filter out blanks?
= SORTBY( FILTER(numbers, numbers), RANDARRAY(COUNT(numbers)) )
gives the remaining options in random order within the smaller array.
If, on the other hand, you do wish to return the blanks but have them packed at the start or end of a fixed length array then
= SORTBY( numbers, IF(numbers, RANDARRAY(75), 0) )
will do that (a 1, rather than 0, will place the blanks at the end).
When I said I wish to avoid filter, I meant I want to avoid applying a filter to the array after the formula has shown a result. I was unaware that filter is also a function. Additionally, including the large function is unnecessary. That's something I did as I have just recently started using it.
