Auto re-order priority list

Question

I'm trying to create a numbered priority list in Excel where after re-prioritizing any number of rows the succeeding numbers in the list update in sequential order. For example, if I reprioritize 5 to 1 the previous 1 becomes 2 and each succeeding number updates. Similar to how you can re-order columns in SharePoint. I was hoping there is a formula for this or someone can point me to an example, template or video on how to accomplish this task. FYI- I prefer a formula over VBA.

Answer 1

Could you share some dummy data and expected result?

Based on your description, it can be done by custom list for sort.

Best Regards,

Snow Lu

Answer 2

I haven't got that far yet, but here is a dummy list: Basically, if I want to re-prioritize by changing Wake-up to 1, I'd like to simply change 6 to 1 and everything updates and re-sorts, i.e., drink coffee becomes 2, eat breakfast 3, and so on. Or if I change warm up jet to 4, watch news changes to 5 and everything that follows updates. Hope I'm explaining that well enough.

Priority	Task
1	Drink Coffee
2	Eat Breakfast
3	Feed cat
4	Watch News
5	Let dog out
6	Wake-up
7	Listen to affirmations
8	Warm up jet
9	Let dog in
10	Go to work

Answer 3

Add a custom list for sort. Then you can use that list to sort your priority.

Result:

Answer 4

I've never used custom list before, but this seems hard coded and not what I'm looking to accomplish. Reprioritizing could take place anytime and with any task. For example, today I may make 6 the first priority, but tomorrow 3. This was just a dummy list of task. The list will also change weekly- actual work related task. Or I may want to change 5 to 2. It won't always be the same and could be any combination of changes.

Before Change

Priority	Task
1	Call maintenance
2	Meeting with Joe
3	File work orders

Change 2 to 1 the list resorts and updates to this

Priority	Task
1	Meeting with Joe
2	Call Maintenance
3	File work orders

Or change 3 to 2 from 1st table the new order is

Priority	Task
1	Call Maintenance
2	File work orders
3	Meeting with Joe

I believe this is going to require a formula with some helper columns. I just don't have enough experience to come up with the solution. Again, I reference the way you can change the column order in SharePoint as my example.

Answer 5

Try this one. Add one help column you can input any number in it. It will rerank from small to large.

Formula in B2

=VLOOKUP(LARGE($G$2:$G$11,12-ROW()),$G$2:$H$11,2,)

If you have 100 task, change 12 to 102 which is 100 +2