Forum Discussion
Sortby
=SORTBY(D2:D76,RANDARRAY(LARGE(B2:B76,1)))
This returns a random array based off the array in D2:D76.
The original array has a possibility to contain blank cells. I do not want those blank cells to be displayed in the SORTBY. Is there a way to do that, that doesn't involve using the filter?
- Pass the array through a FILTER function:
=SORTBY(FILTER(D2:D76,D2:D76<>""),FILTER(RANDARRAY(LARGE(B2:B76,1)),D2:D76<>""))
4 Replies
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.
- Pass the array through a FILTER function:
=SORTBY(FILTER(D2:D76,D2:D76<>""),FILTER(RANDARRAY(LARGE(B2:B76,1)),D2:D76<>""))I never knew a filter could be applied this way. Its something I must look into further.
This works perfectly! Thanks a bunch!
