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
Forum Discussion
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.
- Agreed
- 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.
Bazz02 Depending on your needs and below assuming Excel 365, it can be done in multiple ways and without a macro-button.
Having data in a (pyamas) table you can calculate the rank using a column formula
=XMATCH([@Value];SORT([Value]))
...finding the value on the same row (@ = single cell intersection) among all values sorted, and returning the number of the first match.You may sort that column using the arrow in cell C2
Since it is an interesting education example I added some more solutions outside the table;
Cell E3 sorts the table each time data changes. It is a formula in only one cell but it 'spills' the result to as many cells that are needed.
=SORT(Table1[[Text]:[Value]];2)
G3 isolates the percentages in F3:F8 by addressing E3# (includes the full spill area) and intersects it with column F using the multiple cell intersection character space.
=XMATCH(E3# F:F;E3# F:F)If you need to redesign the layout, it may be more handy to not include a column reference and then INDEX is a solution where row number zero returns all the rows and the two tells the second col.
=XMATCH(INDEX(E3#;0;2);INDEX(E3#;0;2))LET is yet a rather new possibility to build a complex formula step by step. Use alt+Return for line breaks.
The last row/parameter is what is shown as the output of the calculation.
=LET(range;Table1[Value];
data;SORT(range);
output;XMATCH(data;data);
output
)
