finding lowest number of moves to sort a list

Hello everyone. This is my first post.
In an application I use for work I have a list of 41 items that have to be sorted in a specific order.
Unfortunately the only way to order it, it is to move each item up and down until it reaches the desired position (and of course doing this changes the position of other items)
Now, as this is a tedious job I need to do over 500 times, I wanted to see if there is a quick way to find using VBA the lowest number of moves to have them all sorted correctly.

Let me be more clear with an example with just 4 items.
I have 4 colours that have to sorted as:
1. Yellow
2. Blue
3. Green
4. Red

Given an initial order:
1. Yellow
2. Red
3. Green
4. Blue

The quickest way is:
Move Red down by 2 positions
Move Green down by 1 position.

I know that it can also be like
move Blue up by 2 positions
Move red down by 1 position.

I actually do not care, as long as I get the lowest number of moves.

Is there a way I can get a VBA showing me the shortest possible list of moves I have to do to sort the full list the way I want?


Thank you in advance to anyone that can help me.

Hi & welcome to the board.
Why not create a custom sort list & use that?
For more info have a look here
https://www.ablebits.com/office-addi...-custom-lists/

Hi Fluff13
actually that is not what I need cause I do not need to sort the list in excel, this is done by clicking an arrow down or arrow up in the software I use.
What I am looking for is the lowest number of "clicks" i need to do to achieve the result I want...

Are you testing some sort of theory? Otherwise this doesn't sound to resourceful.

No theory. Pure practice, unfortunately.
I have a list of 41 items and I only can move them up and down. But they are not in order and I wanted to see if using excel I can get the shorte list of moves to have them in the order I want

The solution you suggest may work.
I was just trying to see if I can get a list of moves that will end up making the list to be in the order I want. Considering that if you move one item up, automatically you are changing the position of all the others below, I wanted to see if if there was a way to determine the moves to do to get the final order without wasting time...
Sorry I really don't know how to express myself clearer, not being English native it's a bit difficult

Thank you

There are more sort algorithms than you can shake a stick at. Some are more efficient sorting small lists (e.g. 'Bubble Sort') and others are more efficient at large lists. There is no telling ahead of time which algorithm is the most efficient for a specific set of data, although there are algorithms that will indicate general sort efficiency based on list size, and how 'out of order' the list is to start with.

In the past I've used the following algorithms:
a. Bubble Sort (extremely small code size and efficient use of memory - very important in the old days when mini-computers has 32 Kilobytes of memory and 1 Megabyte Hard Disk media was 15 inches in diameter and cost $300 US)
b. Insertion Sort
c. Shell Sort (which has many variations)

By eyeballing the data, your manual algorithm is probably an 'Insertion Sort', where you insert data where you think it will fit best.

Lewis

Ok thank you. Can I replicate an algorithm using vba?

To better explain what I mean is:

Going back to the colours example:

Initial is:
Red
Blue
Yellow
Green

Order I want to achieve:
Blue
Yellow
Green
Red


Now, I can do:

Move up Blue by 1
Move up Yellow by 1
Move up Green by 1

OR - and this is what I wanted to see if was possible to get using a macro

Move Red down by 3.


What I need from the vba is the list of the moves! not that the macro sorts the order.