Forum Discussion
Solved
How to ignore blank cells when using the SORTBY formula
I have a SORTBY formula that spills a list from Column A into Cell C3. The list is sorted by the words' length, from smallest to largest. However there are blank cells in the list that I wish the formula to ignore (please see example below). Is this possible?
The blank cell appear at the top of the spill list (cell C3) which I would like to not happen.
Thank you.
13 Replies
Hello! I'm wondering if you can help me out.
I am trying to use Excel to randomly shuffle names for a work schedule (top image). However, if let's say "Name3" is on vacation, then I need to have them removed, but it creates a "0" in the 2nd column (bottom image, Cell D137).
This is similar to what the orignal poster asked, and I'm wondering if there's a way to get rid of the 0 (or alternatively put the 0 to the bottom of the list).
The function I am currently using is this:
=SORTBY(C86:C93,RANDARRAY(ROWS(C86:C93)))If I add in your "LET" function, it doesn't do anything and still keeps the 0 in there.
Ideally the 2nd column would just be shorter with blanks for however many people AT THE END OF THE LIST in column D, or the 0 is at the end of the list. This is because then in another section of the excel sheet, I would have "=D135", "=D136", "=D137", and so on next to a Task name (for work). But then as you can see, Cell D137 would have the 0 when I want a name, otherwise it assigns "0" to a task instead of "Name1" (assuming it's possible to shift the following cell up).
If this isn't possible, is there a way in the actual formula where I can tell it to exclude certain cells? So for example, I want it to randomly sort Name1-8 in column C, and have the sorted results in column D. But let's say "Name3" is absent, so I want it to still sort the names in column C into column D, but EXCLUDE Cell C125 (assuming "Name 3" is absent)..
Thanks!
With data in C86:C93 with possibly blanks:
=LET(Filtered, FILTER(C86:C93, C86:C93<>""), SORTBY(Filtered, RANDARRAY(ROWS(Filtered))))
- Thanks @HansVogelaar, the formula worked but, it is not sorting in alphabetical order?
bassammahfouz
If you read carefully, the initial request was to sort by the Length of each value, what @HansVogelaar formula does
If you want to sort your Filtered data A-Z, adjust as:
=LET(Filtered, FILTER(A3:A12,A3:A12<>"",""), SORT(Filtered))or simply: SORT(FILTER(A3:A12,A3:A12<>"",""))
Hi, when I try by copying, pasting and adjusting the formula, it returns just 'True' and 'False' values. What am I missing?
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
