Excel VBA How does Excel sort duplicate values?

Question

I'm wondering how Excel would sort identical values. I have ID numbers, but there are instances where there would be a duplicate ID number. I'm unsure about how Excel would decide how to order these? Would it just look at the information in the adjacent row and then compare those values as part or the ordering?

If I had:

Column A | Column B
166          C
166          A
166          B

And then sorted on Column A in descending order, would I get:

Column A | Column B
166          C
166          A  
166          B

Or would I get:

Column A | Column B
166          A
166          B
166          C

Thanks.

You can add extra columns as tie breakers if you want. But in the absence of that, the order of rows with equal values is essentially random. The sort is unstable. — Code Different
– Code Different, Commented Nov 9, 2015 at 21:58
While I don't know the answer, I assume it would just read columns left to right. But you'd be wise to be explicit and specify a 2nd column if you care about it. — Marc
– Marc, Commented Nov 9, 2015 at 21:59
@pnuts - even more of a bother than typing out this well thought out question! :) — Scott Holtzman
– Scott Holtzman, Commented Nov 9, 2015 at 22:01
I don't think Excel uses information in adjacent columns unless you specifically tell it to by adding those to the "sort" columns defined via the UI dialog. — Tim Williams
– Tim Williams, Commented Nov 9, 2015 at 22:12
spreadsheet and database sorts are usually stable. Adjacent cells not specified for the sort are ignored when comparing rows. — rcgldr
– rcgldr, Commented Nov 9, 2015 at 23:01

rcgldr · Accepted Answer · 2015-11-10 03:47:48Z

3

Excel's sort is supposed to be a stable sort, so the order of equal elements should be preserved. You should be able to do something similar to radix sort, sorting rows doing multiple sorts, from least significant column to most significant column. You can sort rows by 3 columns at a time.

If using Visual Basic to do the sort, note that selection sort and quick sort are not stable. Use insertion sort (stable) or bubble sort (stable) instead. I don't know if it's possible to implement merge sort (stable) with VBA.

A non-stable sort can be made stable by including the row number in a compare as the least significant comparator, if this is possible with VBA.

edited Nov 10, 2015 at 3:47

answered Nov 9, 2015 at 22:14

rcgldr

29.4k3 gold badges41 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

chris neilsen Over a year ago

...so the order of equal elements should be preserved: sounds reasonable (and is supported by a quick test.) Do you have an authoritative reference to support this claim?

rcgldr Over a year ago

@chrisneilsen - microsoft example for how to sort rows by more than 3 columns. Spreadsheet and data base sorts are usually stable sorts.

shg Over a year ago

Excel ALWAYS performs stable sorts.

Charles Williams Over a year ago

VBA QuickSort is not stable

shg Over a year ago

And lots of other sorts aren't either. But sorting from the UI is.

|

Collectives™ on Stack Overflow

Excel VBA How does Excel sort duplicate values?

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related