I want to sort some rows of data based on an index like this:

Yes No (Some Data)
No Maybe (Some Data)
Maybe Yes (Some Data)
Often
Stuff Often (Some Data)

I want to order the second column by the first, inserting spaces where there is missing data. I have many sheets with different indexes.

At the moment I dynamically name the rows of data as ranges then compare them one by one with the values in column A. If a row is out of place then it is swapped with the row in its rightful place.

This works some of the time but in certain situations it is possible for a row to be ‘swapped out’ of the loop leaving the bottom rows well ordered but the top in a mess. I suspect a second pass would make abif difference but I’m not sure how foolproof this method is.

I’m sure someoen out there has a much better solution to this problem so I thank you in advance for any input.

Ollie

Ollie,

I'm not totally sure I understand what you're after so I think I'll run through what I think I'm answering first. The table you supplied is really in two parts; column one (LHS) is a pre-specified custom ordering column; every subsequent column (RHS) is a separate data table. The separate RHS table is to be sorted on its first column (column two of the provided table) to the order specified in column one.

If this is the case then you might want to consider the ability of Excel to set up custom lists and then the ability to run a sort procedure by custom list.

You can set up a new list by going through the menus to Tools -> Options... -> Custom Lists. From here you can import a new list from a range of cells at the bottom of the dialogue box or manually type one in should you desire. Please note that this list is not dynamically linked to the range provided but is rather a labour saving device to stop you typing out the list items when you already have them.

You can then sort any data table you want using this custom list by going through the menus to Data -> Sort... -> Options... . You should then set the drop down box labelled First key sort order to your newly created custom list and your sort procedure will be done by your custom list.

All of the above can also be automated via VBA, if that's your thing. I won't go into the code but if you want it then macro recorder is your friend :)

Maccas

Hi,

Thanks for your reply, that was pretty much much what I was asking. It's a useful tool but I can't seem to find away to make it sort whole rows of data, it will only sort the headers in the first cell in each row. Does anyone know of a way to extend the custom list functionality so that the field headers are sorted along with their data?

I do now have a solution to the orriginal problem, but it's a bit clunky, using a bunch of nested loops and ifs, If I could find a way to utilise this function I'd bet it would run significantly faster.

Thanks again.

One of the options on the Excel sort procedure is a radio button for Header row / No header row. Is this not what you need to toggle?

He he,

Always the simple ones that get you...

yeah that did it, I haven't written the automation script yet but I'd imagine it will be a tenth of the size of the monster I'm using at the minute.

Thanks again, your help is much appreciated.

Ollie