PDA

View Full Version : Solved: Sorting array containing a mix of strings and numbers



Digita
09-11-2008, 10:34 PM
Hi guys,

Applying DRJ's code (http://www.vbaexpress.com/kb/getarticle.php?kb_id=103) in the Kb to sort a 1-D array which contains numbers and text. If an array contains: "James", "Mary", "Tom", "Beth", "Bob", "Chris", "Daniel", "Lari", "Al", "Teresa", 10, 2, 1, the sorted output shows: 1, 10, 2 and followed by names in alphabetical order.

How can we have the number part to be shown as: 1, 2, 10?

Thanks & regards

Oorang
09-11-2008, 11:23 PM
Well there are a couple different ways. The fastest (in terms of run time) is to use a numeric datatype, such as long, for the main array. Then all comparisons will be numerical. But if speed is an issue, you should probably consider a different sorting algorithm then bubble sort anyhow. (See http://www.devx.com/vb2themax/Article/19900 for a great read of various types of sorting algorithms and their performance. Code is included.)

If the data types are mixed together with text and numbers, but numbers when they occur will be "pure" then just use VarType or Isnumeric to create a case for the numbers.

Lastly if you want "numeric sorting" on mixed data (ex 123 High St) then you will probably need to write a custom compare function instead of using a primitive operator for comparison.

Frankly it might be less painful to just use Excel's built in sorting capability before loading the array. I suppose it depends on the needs of the application.

Digita
09-11-2008, 11:32 PM
Thanks Aaron.

Yeah, totally agree with you, I always use the native sorting function inside XL as much as possible.

Kind regards

kp