Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 17th, 2006, 12:10 PM
Registered User
 
Join Date: Nov 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default sorting based on an index

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
 
Old November 20th, 2006, 01:07 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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

 
Old November 21st, 2006, 05:13 AM
Registered User
 
Join Date: Nov 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old November 21st, 2006, 05:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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?

 
Old November 21st, 2006, 05:31 AM
Registered User
 
Join Date: Nov 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Pls help - Sorting based on condition kalwinhobbess Crystal Reports 0 September 10th, 2007 10:47 AM
Sorting data in to groups based on rules cube VB.NET 0 April 11th, 2006 06:44 PM
sorting based on substring-before Kabe XSLT 1 October 14th, 2005 09:54 AM
Sorting based on formula in group footer NixyJ Crystal Reports 0 August 27th, 2004 06:37 AM
Datagrid sorting by non alphabetical sorting? LLAndy VS.NET 2002/2003 1 July 15th, 2004 01:20 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.