Sort column B while keeping column A's order

Question

In Excel, I have 2 columns. I want to sort the second one in alphabetical order while keeping the (custom) order in the first one (think std::stable_sort where equality is based on the first column only). Example:

dsf   k
www   d
www   a
azd   q
azd   e
azd   b

should become

dsf   k
www   a
www   d
azd   b
azd   e
azd   q

The sorting wizard doesn't seem to have any "keep current order" option. I could define the custom order but I have more than a thousand different elements; that would be a real pain to do and this custom order can change at any time.

Answer 1

I’ll assume that your data start in Row 2, and that Column C is available for use as a “helper column”.  Put

=IF(A1=A2, C1, C1+1)

into cell C2.  (If Column C isn’t available, use one that is.  If your data start in Row 1, either insert a blank row, or (still) put the above into cell C2, but put 1 into cell C1.)  Drag/fill down.  This (Column C) now “documents” your custom order; e.g., C2 = 1,C3 = C4 = 2, andC5 = C6 = C7 = 3.  Now sort on Column C and Column B.

Answer 2

I think your best bet is to use custom orders. If it doesn't have to be updated to often you could do it through FILE>OPTIONS>ADVANCED>EDIT CUSTOM LISTS... and just import the entire range. Then use that when sorting.

VBA solution would be something like

Sub sort()
Dim iCustListNum As Integer

iCustListNum = Application.CustomListCount + 1
Application.AddCustomList ListArray:=Range("A2:A100")

On Error GoTo err:

'Sort your list
Range("A1:B100").sort Key1:=Range("A1"), Order1:=xlAscending, OrderCustom:=iCustListNum, _
    Key2:=Range("B1"), Order2:=xlAscending, Header:=xlYes, _
    MatchCase:=False, Orientation:=xlTopToBottom

'Clean up
err:
Application.DeleteCustomList ListNum:=iCustListNum

End Sub

Answer 3

Quick and dirty:-

1. Copy the contents of column A into notepad, then recopy to the clipboard.
2. Select both columns.
3. Select the sort function and sort column A using the "custom list" order.
4. Enter the data from the clipboard into the custom list.
5. Add a level to the sort function and then sort by column B, "smallest to largest".