Apr 28 2020 09:20 AM
This is just and aesthetic issue.
I have a table with around 7 headings for a simple account information table. Not all accounts have a value for each heading/field so on entering data those cells are left blank. To make it easier to find each row of account information I have used the SORT function so I can continue to add new accounts on the original table as I think of them and they are displayed alphabetically on a different sheet as the table is updated.
The displayed results from the SORT function return "0" zero in every field which is blank in the original table and as the Date column is formatted to display a date it returns 00/01/1900. I would like it to return blank cells from blank cells.
Is there anyway of getting the SORT function to return a blank/empty cell or it simply a result of the entries being processed as data?
Apr 28 2020 10:07 AM
Jun 08 2021 08:11 AM
Apr 25 2022 02:58 PM
Thank you, this worked perfectly.
Apr 25 2022 03:50 PM
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.
Apr 25 2022 04:51 PM
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.
Apr 25 2022 09:01 PM
Apr 26 2022 01:57 AM - edited Apr 26 2022 02:04 AM
Apr 26 2022 01:57 AM - edited Apr 26 2022 02:04 AM
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)
Apr 26 2022 09:31 AM - edited Apr 26 2022 09:39 AM
Apr 26 2022 09:31 AM - edited Apr 26 2022 09:39 AM
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<>""))
Apr 26 2022 12:34 PM
You didn't get the syntax of the LET function quite right. As you observed earlier, the object is to assign local names to stages of the calculation. This is achieved by a list of comma-separated parameters which are taken in pairs, with a variable followed by its definition. To make this clearer, I use alt/enter to separate the parameter pairs to individual lines.
= LET(
filtered, FILTER(Data, Dates<>""),
sorted, SORT(filtered),
IF(sorted<>"",sorted,""))
This shows the zeros as blank fields. If you are happy with the 0s, the last line goes, in which case you might as well nest the 'filtered' formula within SORT.