Forum Discussion
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.
- Hi, What do you mean by 'order from 1 to 20'? What is 1, what is 30? Kind regards Hans
14 Replies
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.
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.
- I 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.
- Hi, What do you mean by 'order from 1 to 20'? What is 1, what is 30? Kind regards Hans
if you have 20 rows that have been sorted say by their %
then in another column you need show the ranking/position from 1-20
which would be 1-20 if all % were different but if for some reason some are the same
ie the top 3, then their ranking would be 1 but the next position would be 4th and so on to the bottom if that makes sense.
- Hi,
Did you try the RANK function?
https://support.microsoft.com/en-us/office/rank-function-6a2fc49d-1831-4a03-9d8c-c279cf99f723
Kind regards
Hans
