Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Multi-columnn custom sorts

  Asked By: Jordon    Date: Sep 21    Category: MS Office    Views: 856
  

I have a worksheet column that contains cells with a list of words
in it, such as:

Example_1-02
Example_11
Example_10
Example_1
Example_12
Example_100

When you sort this using the default A-to-Z Excel sort, it orders
them in the following way:

Example_1
Example_10
Example_100
Example_1-02
Example_11
Example_12

However, this isn't the order in which I want them. I can get them
in the 'corrct' order by using a Quicksort routine that gives the
following order:

Example_1
Example_1-02
Example_10
Example_100
Example_11
Example_12

Now, the complicated bit... this works fine when I'm sorting a
single column, but how would I do about dealing with things if there
were multiple columns, and I wanted the other data to remain
associated with values being sorted? Any pointers?

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Anne Powell     Answered On: Sep 21

Post your Quicksort routine  and I'm sure it'll get tweaked.

 
Answer #2    Answered By: Tate Thompson     Answered On: Sep 21

The code for the quicksort is essentially the one from
http://www.devx.com/vb2themax/Tip/18973.

For sorting  a single column, all I do is loop through the cells  loading
them into an array, pass that array to the quicksort routine, and then
write the sorted array back into the worksheet  using another loop -
admittedly, it's probably not the best way, but it works, is fairly
easy for others to understand, and has th eadvanyage of sorting both
text or numbers.

Does that help any?

 
Answer #3    Answered By: Charlie Evans     Answered On: Sep 21

One option would be to use the quicksort but instead of writing it back in its
original location, write it to a new sheet and then use a series of vlookup
formulae to fill in the columns.

Alternative would be to step through the sorted array, find the relevant row
and copy it to a new sheet.

If you wanted  to you could finish by copying the cells  from the new sheet back
to the old and deleting the new.

 
Answer #4    Answered By: Jawna Mohammad     Answered On: Sep 21

I was kind of thinking along the lines of loading each column  into
an array. Then loading the column i wanted  to sort  by into another
array,then sort it. By comparing the sorted and unsorted arrays it'd
be possible to find the index of the value in the original array.
Then it's just a case of extracting that same index from all the
other arrays and writing them back into the worksheet. The only
thing i'm not certain about is how it'd deal with things  if two
values in the sorted array were identical.

Your worksheet  approach might be a slightly easier way of doing the
same thing!

 
Didn't find what you were looking for? Find more on Multi-columnn custom sorts Or get search suggestion and latest updates.




Tagged: