Forum Discussion
Sort Function (SortBy) - Trying to get empty cells to return empty cells using the sort function
A similar solution,
= LET(
sorted, SORT(array),
IF(sorted<>"", sorted, ""))
This is clear to read and does not require the SORT to be performed twice.
- What if you are sorting 2 columns, A & B and you wanted to apply the blank test only to A? For example, if A contained a Date and B contained an Item related to that Date, and if you didn't want neither A nor B to appear in the sort if A was blank, even if B had an item in it?
What you describe would appear to need FILTER as well as SORT.
= LET( filtered, FILTER(table, Date), sorted, SORT(filtered), IF(sorted<>"",sorted,""))In response to your earlier comment on LET, I find that the changes in modern Excel require thought processes that are utterly different from those of the traditional spreadsheet. So much so, that I frequently find it difficult to construct answers that will be of value to the OP.
In the present case, were you to want to apply the same filtering process to many tables, you might pass the table names to the formula by using a Lambda function
= LAMBDA(array, LET( column1, INDEX(array,,1), filtered, FILTER(array, column1), sorted, SORT(filtered), IF(sorted<>"",sorted,"") ) )(table)That looks intimidating, but if one uses a defined name such as 'ValueScheduledλ' to refer to the formula then many data tables could be filtered and sorted with no more than
= ValueScheduledλ(table1) = ValueScheduledλ(table2)Thank you for that. I'm unable to make the first filtered formula work. I have two Ranges that I'm using from the Data Tab. Data which is A2:B35 and that includes the dates and the Item related to the dates. Then I have a Dates only range called Data_Dates which is A2:A35.
Here is my formula so far.=LET(filtered(FILTER(Data,Data_Dates<>""),(sorted,SORT(Data),IF(sorted<>"",sorted,""))
However, using a much more simpler formula, does work.
=SORT(FILTER(Data,Data_Dates<>""))
Even better. I was not familiar with the Let function and I like the ability to assign a variable or a computation to a name.
