Add the ability to build a custom sort order. instead of the options being A-Z and Z-A. Display list data and select in which order to display it.
One way to do a custom sort is to:
1. Create a small "mapping" file with 2 columns that maps your custom values to a number that indicates the order you want and upload it as a datasource, e.g.,
2. Then use SORT( LOOKUP( data, A:A, B:B), ascending numeric, data ) where data points to the data you want to SORT.
Please let us know if this doesn't address your scenario, in which case, please also provide a few more details so we can help.
Thanks for the suggestion! I want to make sure I'm understanding your request correctly.
It sounds like you want to specify the order of list. So for example, if the data is:
You want the data to appear in the order: X-Small, Small, Medium, Large, X-Large. Right?
I will pass this idea along to our Product Manager. In the meantime, you should be able to get the desired results by using an ARRAY and LOOKUP function.
Can you show how using an Array and Lookup addresses this requirement please?
Thank you Janice,
I feel like I am getting very close, but not quite there yet.
I uploaded an additional data source for the mapping.
The main column of my table now looks correct:
The first and last @A:A are my actual data, and the second one is column A from the mapping file.
Note that I needed grouping as well as I have multiple "Medium" items and I want only one to show up with a sum of all medium items.
So my next challenge is now to match the next column (let's say it's the sum of number of shorts sold).
The formula I had there was:
(sum up all the "AMER" shirts, per size)
But now the data is skewed. How do I match this GROUPBY to the sorted values in the first column?
I hope my question is clear! Thanks for all your help so far.
To align your # of shorts sold column with the first column requires a LOOKUP, where !column1 is a REF to your first column
GROUP( SELECT( @A:A, @B:B="AMER")),
COUNTDISTINCT( SELECT( @A:A, @B:B="AMER")) )
If this isn't clear or doesn't address your question fully, send a ticket to email@example.com.
Hi there, I have a similar requirement. I have a table with a column that contains (to save some space) a concatenation of 2 things result being s.th. like 23 (187), which means 23 this month, 187 for all time. Now, when I sort, I'd like to simply sort by the column "this month". but because it's alphabetical according to the concatenated string, it won't work. It would be nice, if this could be added! Thanks!
Is there any way to disable sort (by clicking on the header) in a table?
No, there is no way to disable the sort by clicking a table header.