Automatically moving rows in office 365 excel based on rank or largest to smallest of a said column

[adstristar]

Hi everyone,

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.

[#2]

@adstristar 

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:

1. First, ensure that your table has headers and is formatted as an Excel table. Select the entire table range, including the headers.
2. Go to the "Formulas" tab in the Excel ribbon and click on "Define Name" (or "Name Manager" in some versions).
3. In the "Define Name" dialog box, give a name to your table range, e.g., "MyTable". Click "OK" to save the name.
4. Create a new column next to your existing table, where you want to display the ranking. In your example, this would be column 8.
5. In the first cell of the new column, enter the following formula and adjust it based on your column references:

=RANK([@YourColumn],$YourTable[YourColumn],0)

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).

1. Drag the formula down to populate the remaining cells in the column with the rankings.
2. Now, go to a different location in your worksheet where you want the sorted table to appear. This can be a separate sheet or a different range on the same sheet.
3. In the first cell of the new location, enter the following formula:

=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.

1. Press Enter to apply the formula, and the sorted table will be generated based on the rankings in the specified column.

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.

[#3]

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

[#4]

@adstristar 

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:

1. Insert a new column next to your existing table, where you want to display the ranking. In your case, this would be column D.
2. In cell D2, enter the following formula and drag it down to populate the remaining cells:

=RANK(C2,$C$2:$C$25,0)

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.

1. Now, select the entire table, including the headers, and go to the "Data" tab in the Excel ribbon.
2. Click on the "Sort" button in the "Sort & Filter" group.
3. In the "Sort" dialog box, choose the column that contains the rankings (Column D) as the "Sort by" column.
4. Select the "Values" option in the "Sort On" dropdown.
5. Choose "Largest to Smallest" in the "Order" dropdown.
6. Click "OK" to apply the sorting. The rows will now be sorted based on the rankings in Column D.

Make sure to adjust the column references and range as per your actual data.

[#5]

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?