Priority Queue

**coachklein** · 10-27-2011, 02:32 AM

Hey Folks,

I've already searched high and low, even so, if this is a duplicate post, feel free to point me in the right direction. I'm trying to create a priority queue similar to what you would see in Netflix.

Column A contains list items (e.g., movies, tasks, whatever)
Column B contains queue number 1-10+ increasing with amount of entries

If Column B is changed, it will move associated list item with it. All other list items will move up or down accordingly. No gaps and no duplicates. If a new item is added, it can be prioritized on the fly and the queue updates dynamically.

Examples:

Original list
--------------
Item A - 1
Item B - 2
Item C - 3
Item D - 4

Change priority up (note how everything below Item D shifted one number down in priority)
--------------
Item A - 1
Item D - 2
Item B - 3
Item C - 4

Change priority down (note how everything above Item A shifted one number up in priority)
--------------
Item D - 1
Item B - 2
Item A - 3
Item C - 4

Add new item (Item E)
--------------
Item D - 1
Item B - 2
Item E - 3
Item A - 4
Item C - 5

I've seen solutions that can almost accomplish all features, but fall short. I'm certainly not an expert, so I could use some help. Use of VBA is fine. Thanks in advance!

Update: If the Column B (priority rank) could be a select-able drop down, that would be a great bonus, but not required.
Update 2: This thread is super close to what I would like to do, but is seems to have fizzled - http://www.excelforum.com/excel-gene...n-columns.html
Update 3: One last thing, the ability to have multiple columns associated to the priority column would be helpful (e.g., First Name/Last Name/Birthday/etc.)

**brokenbiscuits** · 10-27-2011, 05:47 AM

How about the attached?

**coachklein** · 10-27-2011, 11:02 AM

Wow, that is really close!

If you can, I need to be able to add entries (rows) on the fly (which I wasn't able to do without causing an problem). When I added Item E - 1, it shot right up to the top and everything else reordered themselves perfectly, but when I tried to re-prioritize Item E to priority 5, it just stayed at the top and didn't move. Trying to re-prioritize further causes other issues from there on. I've attached my findings for reference.

Secondly, I need to be able to add a few more columns as things change.

Example:

Original
-----------
Item A - Red - 1
Item B - Blue - 2
Item C - Yellow - 3
Item D - Green- 4

Later on I might need to add another column...
-----------
Item A - Red - Square - 1
Item B - Blue - Circle - 2
Item C - Yellow - Triangle - 3
Item D - Green - Hexagon - 4

**brokenbiscuits** · 10-27-2011, 11:49 AM

Ok, hopefully the attached should be better.

Basically, a header row was required because the macro uses the sort function.

You can now also add as many columns as required, but you do need to keep the header 'Ranking' - or if you want to change this, change the references to 'Ranking' in the macro to match.

**coachklein** · 10-27-2011, 01:32 PM

This is great!

I'm gonna get a little greedy here with one more request. When I delete a row, it would be nice for everything to move up a rank below the item that was deleted so the list is still sequential without gaps.

I don't want to push my luck, you've been incredibly helpful, but this will make the project work exactly how I need.

Example:

Original
-----------
Item D - 1
Item B - 2
Item E - 3
Item A - 4
Item C - 5

Remove item B
-----------
Item D - 1
Item E - 2
Item A - 3
Item C - 4