Forum Discussion
How to ignore blank cells when using the SORTBY formula
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))))
HansVogelaar Amazing! Thank you so much!
- 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<>"",""))
- Thank you
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?
- Wins Ave PD1 PD2 PD3 PD4 PD5 PD6 PD7 PD8 PD9 PD10 PD11 PD12 PD13 PD14 PD15 PD SUM PD AVE
Player 1 4 -5.166666667 -23 -18 -10 -9 4 25 -31 -5.166666667
Player 2 5 -8.166666667 -18 -15 -14 -3 -3 4 -49 -8.166666667
Player 3 5 -8.666666667 -25 -21 -10 -8 -2 14 -52 -8.666666667
Player 4 2 13 -6 -2 9 16 23 38 78 13
Player 5 4 -1.833333333 -15 -9 -8 -2 2 21 -11 -1.833333333
Player 6 2 12.83333333 -9 -2 12 16 25 35 77 12.83333333
Player 7 5 -6.833333333 -26 -17 -7 -4 -3 16 -41 -6.833333333
Player 8 6 -9.666666667 -25 -12 -8 -8 -3 -2 -58 -9.666666667
Player 9 6 -10.66666667 -21 -15 -12 -7 -6 -3 -64 -10.66666667
Player 10 4 -12.5 -27 -25 -21 -13 1 10 -75 -12.5
Player 11 3 -7.5 -19 -7 -5 1 -30 -7.5
Player 12 5 -8.666666667 -24 -13 -10 -7 0 2 -52 -8.666666667
Player 13 4 -7.833333333 -30 -19 -12 -8 1 21 -47 -7.833333333
Player 14 5 -18 -30 -28 -27 -15 -11 3 -108 -18
Player 15 5 -10.16666667 -28 -16 -12 -11 -10 16 -61 -10.16666667
Player 16 4 -6.333333333 -25 -20 -12 -10 5 24 -38 -6.333333333
Player 17 4 -3 -20 -14 -7 -2 1 24 -18 -3
Player 18 4 -6.8 -18 -11 -10 -4 9 -34 -6.8
Player 19 5 -11 -21 -18 -15 -9 -9 6 -66 -11
Player 20 0
Player 21 0
Player 22 0
Player 23 0
Player 24 0
Player 25 0
Player 26 0
Player 27 0
Player 28 0
Player 29 0
Player 30 0
Player 31 0
Player 32 0
Player 33 0
Player 34 0
Player 35 0
Player 36 0
The sample has no formulae included. The only Static entries are The First Column and the first row.
Is that where my issue lies?
- Amazing!
Excel is as brilliant as those with the knowledge on how to use it. You are brilliant. Thank you so much, this is just what I need.
cheers
