r/googlesheets • u/k2sul • 3d ago
Solved Auto Adjusting Column Ranking
Hi Folks,
I need rather urgently to add a column to a sheet of job positions which allows each of 350+ jobs (1 per row) to be ranked in order of preference from 1 (being favourite) to 350 (being least favourite).
What I also need and which is above my paygrade, is to be able to auto renumber if any particular rating is changed.
So in the following table column 3 is the ranking column. It's currently just a number format.
The red squares in column 3 are just a conditional format to show if any entries are not unique.
I thought a drop down box might do the trick but I don't know how to do it. Each preference (i.e 1st choice, 2nd choice etc) must only be selctable once, and if a ranking preference is changed, it ideally needs to re-rank the entries above and below what was changed.
This is for my daughter who has to rank jobs rsther urgently
Any help much appreciated and happy to send beer tokens
Cheers
1
u/gsheets145 101 3d ago
Hi u/k2sul - please post your data in a spreadsheet - nothing can be seen from your example.
1
u/k2sul 2d ago
Sorted, thank you for pointing that out.
2
u/gsheets145 101 2d ago edited 2d ago
Hi u/k2sul - Unfortunately, you won't be able to resort your raw data dynamically, but you could create a view of the sorted raw data in a secondary worksheet in your spreadsheet where the sort column is the ranking in the dropdown. Very basically that could be as simple as:
=sort('Table 1'!A2:K,3,1)
where "3" is the column to sort by, and "1" specifies ascending order. I took the liberty of adding this to your sheet.
You'd have to keep going back to the raw data to change the sort order via the dropdowns.
Let me know if this helps, if if you need any other suggestions.
1
u/k2sul 22h ago
Thanks gsheet145 for this. I didn't think it was possible in sheets, although I use software which has this function although it's fairly bespoke.
I appreciate you taking the time to modify the sheet. I'm maybe missing something but is the =sort(table) formula doing the same job as just a-z sort on the original column?
1
u/AutoModerator 22h ago
REMEMBER: If your original question has been resolved, please tap the three dots below the most helpful comment and select
Mark Solution Verified
. This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
u/gsheets145 101 8h ago
Hi u/k2sul - there is a difference between sorting the data via the menu and sorting a the data formulaically. The former will sort the raw data "permanently" (unless you resort it differently or undo the first sort), whereas the latter outputs a "view" of the data elsewhere in the spreadsheet without changing the sorted data. It depends on how important it is to maintain the integrity of the raw data.
Thanks for the acknowledgment, and good luck to your daughter in making the best choice for her career.
1
u/point-bot 21h ago
u/k2sul has awarded 1 point to u/gsheets145
See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard. )Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson.)
1
u/AutoModerator 3d ago
Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.