Excel programming help - which formula to automate a part of a spreadsheet

Occasional Visitor

Hello I am attempting to get excel to sort a column into largest to smallest (easy part) however this task needs to be reset each time it encounters a change in ID in another columns (hard part). The changing column is titled 'tile' (in the screenshot to come). The column needing sorting is called 'diameter_kms'. The screenshot will show what it should look like when finished, however the data I am attempting to work with will have a non-sorted/jumbled diameter_kms column. Hellllppppp there are over a hundred thousand rows to do this for manually, and it'd be nice to have my life back. Should be a quick thing and I've already written most of the command, just a bit stuck. Cheers

1 Reply

Hi there; can't you use a RANK function to rank the kilometers and then VLOOKUP in a pre-sorted sheet?


Add a "Rank" column before the one with the name of the tile.

In the first cell, use =RANK(all column of the kilometers;first cell of the kilometers;1 or 0 depending on the order ascendant/descendant)

Then, create a new sheet with fixed numbers from 1 to N, and then use VLOOKUP to visualize the right tile in the right place.


Tricky situation is when you have same kilometers but different name of the tile ;)