Forum Discussion
Bazz02
Sep 17, 2021Copper Contributor
excel in windows 10.
how to create a button that sorts by one column and then assigns an order in another column from one to 20.
- Sep 17, 2021Hi, What do you mean by 'order from 1 to 20'? What is 1, what is 30? Kind regards Hans
PeterBartholomew1
Sep 26, 2021Silver Contributor
Following up some of the ideas already presented,
is returned by the formula
= LET(
ranking, RANK(Table1[Value],Table1[Value],-1),
output, IF({0,1}, Table1[Text], ranking),
SORT(output)
)
The ranking is placed first because, for some mysterious reason, RANK requires range references and not arrays. The next row is a device to combine the newly calculated rankings with the text column before it is sorted by rank.
- bosinanderSep 26, 2021Steel Contributor
I see that
absolute referencesstatic arrays comes more and more handy.Using CHOOSE allows to combine multiple dynamic arrays.
=SORT(CHOOSE({3\1\2}; Table1[Text]; Table1[Value]; Table1[Rank]))
Also noted that RANK is around for backwards compatibility.
- PeterBartholomew1Sep 27, 2021Silver ContributorI like Tables because of the way in which they adjust to the actual data (as well as providing structured references) so that Excel is not left to infer what is intended by the user; it is explicit.
- bosinanderSep 28, 2021Steel Contributor
[x] Like Tables
I do though stress, during classes, that when you work with column formulas you enter it......and in one step gets the formula, a new column and the column filled with that formula.
When undoing though, it demands three Ctrl+Z. One for each step.
The risk is when making a less visible change and typing Ctrl+Z, it is very easy to not have undone the change as expected. In the rest of the envirnment it is normally one change and one undo.
The only hint is a tiny green corner telling that the cell deviates.I don't have any other suggestion for the interface - I'm confident that Microsoft have come up with the best solution among alternatives. Just notice that it is an area with a risk for unwanted results.
- PeterBartholomew1Sep 26, 2021Silver ContributorAgreed