0

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.

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.

0

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".
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.