Jun 02 2023 09:51 AM
Jun 02 2023 09:51 AM
I have a table on office 365 Excel and what I am trying to do is to have the rows automatically move up or down in order of rank or a said column with values.
So if I have 10 rows in the table and the 8th column is where I have the values that I want to rank largest to smallest and it will move the rows in that order of ranking.
Not sure how to formulate this or where to put onto a table.
I have managed to get a column to rank by number by the column that has the data in like this =RANK([@Column39],$FC$1:$FC$27,) this allows that cell,column to be nunmbered in order of largest and smallest however it will not move the rows automatically.
It looks like I am partway there but not able to get the rows to move. I have put that formular into the top cell of the column and then dragged the formula down the rest of the column.
Any help would be appreciated.
Jun 02 2023 10:49 AM
To automatically move rows in Excel 365 based on the rank or values in a specific column, you can use a combination of the RANK function and the SORT function.
Here is how you can do it:
Replace "YourColumn" with the actual column header in your table and "YourTable" with the name you defined in step 3. The "0" as the third argument in the RANK function indicates ranking in descending order (largest to smallest).
=SORT(YourTable, YourTable[YourColumn], -1)
Replace "YourTable" with the name you defined in step 3, and "YourColumn" with the actual column header you want to sort by.
Now, whenever you update the values in the specified column, the rows will automatically be repositioned based on the ranking.
Note: The SORT function is available in Excel 365 and may not be available in earlier versions of Excel.
The steps were created with the help of AI, all information is untested and without guarantee.
Jun 02 2023 11:23 AM - edited Jun 02 2023 11:26 AM
I have had a go at it just now and it will not populate as it keeps coming up with an error. here is the link to the table https://1drv.ms/x/s!Aux_4hjPdZM3j9dI4Jvt6gV-M2OSkg?e=gZJMPW
Column C is the column that I have the ranking order... its just about it automatically sorting the rows in order of the ranking with 1 being the top row and 4 being on the bottom... or whatever number it will be once populated
Jun 02 2023 11:02 PM
Based on the provided data, it seems that the column you want to use for ranking is "Column3" (Column C). To populate the rankings in a separate column and sort the rows based on the ranking, you can follow these steps:
This formula ranks the values in Column C (Column3) in descending order (largest to smallest) within the range C2:C25. Adjust the range if your data extends beyond this range.
Make sure to adjust the column references and range as per your actual data.
Jun 03 2023 09:20 AM - edited Jun 03 2023 09:58 AM
Hi and thank you for your help,
I have done this and it works to a fashion. When I click sort by custom sort it does indeed do it as you said however what is does not do is sort automatically as i input data. So when I put more data in the table it will show in column c that the ranking position is there however it does not move it automatically. Can this be done without manual intervention? Would I need to use a VBA code perhaps?