19 thoughts on “Building a self-sorting list”

1. There are a lot of ways to do this, and A LOT of those ways could be found in the newsgroup archives. Given the redundant calculations involved in this, udfs could be faster than formulas.

   A more efficient pure formula approach would be to use an array formula in column B, and definitely not use entire column references. If the names in col A spanned rows 1 to 2000, select B1:B2000 and enter the array formula

   =IF(A1:A2000?”,COUNTIF(A1:A2000,”<“&A1:A2000))

   Then you only need one more column.

   C1:
   =INDEX(A$1:A$2000,MATCH(0,$B$1:$B$100,0))

   C2:
   =IF(ISNUMBER(MATCH(ROWS(C$1:C1),B$1:B$2000,0)),INDEX(A$1:A$2000,MATCH(ROWS(C$1:C1),B$1:B$2000,0)),
   IF(ROWS(C$1:C1)”&C1),INDEX(A$1:A$2000,MATCH(COUNTIF(A$1:A$2000,”<=”&C1),$B$1:$B$2000,0)),””)

2. Hi fzz –

   There are pluses and minuses with array formulas, and one of the minuses is that they are very hard, relatively, to edit. Easier to fill down.

   Your indexing number in Column B will only point to the first of a kind, and then the match will repeatedly return that first found. In the example, this meets the intent, but if the adjacent cells are unique and need to be carried with the sort, you need a unique id. That’s the purpose of the COUNTIF($A$1:$A1,”=”&A1) part. Trivial example: Column A is an ordered sort of last names. Column B is first names. One name is Doe|John and another is Smith|John and the desired output is a sort on first names because that’s how they show up in the address book (from real life!). Without the unique id, you’ll get John|Doe twice.

   Excel gives me lots of columns. I like to use them to reveal intermediate steps. Tends to keep the hair in my head. I can noodle out your C2 but it’ll cost me some gray hair ;-)

   …mrt

3. So why use formulas to sort? Wouldn’t Change and/or Calculate event handlers calling the Sort method of specified range objects be far more efficient? Don’t do so in order to avoid macro security?

   If you must have a unique key including duplicates, then with just formulas,

   B1:B2000:
   =IF(A1:A2000?”,COUNTIF(A1:A2000,”B#,B$1:B$2000)) to find the next smallest after the value in cell B#, and that requires single cell array formulas.

4. So why use formulas to sort? Wouldn’t Change and/or Calculate event handlers calling the Sort method of specified range objects be far more efficient? Don’t do so in order to avoid macro security?

   If you must have a unique key including duplicates, then with just formulas,

   B1:B2000:
   =IF(A1:A2000<>“”,COUNTIF(A1:A2000,“<“&A1:A2000)+(ROW(B1:B2000)-ROW(B$1))/ROWS(B1:B2000))

   This is an array formula. To me no more difficult to edit than nonarray formulas. It just takes pressing 3 keys rather than one to enter is. The point here is to avoid calling COUNTIF more than once for each cell in B1:B2000.

   This requires changing the col C formulas as well, to use MIN(IF(B$1:B$2000>B#,B$1:B$2000)) to find the next smallest after the value in cell B#, and that requires single cell array formulas.

5. fzz – on the strong probability that I don’t know something here: to expand the array that an array formula applies to, I select the array, delete the formula, select the new array and re-enter the new formula. Anything else I’ve ever tried gives me a “Can’t edit parts of an array” complaint.

   How do you do it? I’m talking about more that a CSE press.

   …mrt

6. Michael,

   To overcome the problem of CSE you can use Names

   Define Rand_Name =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
   Define Rank_Lst = COUNTIF(Rand_Lst,”<=”&Rand_Lst)

   In B1 type = Index(Rand_Lst,Match(Row(),Rank_Lst,0)

   No need of CSE

7. Opps forgot a close bracket

   In B1 type = Index(Rand_Lst,Match(Row(),Rank_Lst,0))

8. Hi Sam –

   Pressing CSE isn’t the problem…but maybe my approach is ;-) Thanks.

   Both the definition of Rank_Lst and B1 have a Rand_Lst inside. Believe you meant Rand_Name, or you meant Rand_Name to be Rand_Lst?

   …mrt

9. Michael….yes more typos

   Define Rand_Lst =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
   Define Rank_Lst = COUNTIF(Rand_Lst,”<=”&Rand_Lst)

   In B1 type = Index(Rand_Lst,Match(Row(),Rank_Lst,0))

10. To change the size of the array, just select all cells you want the array formula to span and hit F2, CSE.

11. Jan

    In my experience you can only _increase_ the size of an array that way. I’ve always had to do what Michael says to shrink an array. What is the cool way to shrink an array?

    Nice post by the way – this is something I have to do a lot of. I have my own hacky way of sorting but I’ve never had to deal with missing spaces or duplicates before. I’ll mark this page!

12. To increase the size of an array formula’s result, select the larger result range, make one of the cells containing the array active and edit it, then enter as an array formula.

    To shrink the size of an array formula’s result, I find it easiest to select a cell in the array, insert an apostrophe before the initial =, enter as an array formula. That converts all cells in the array to the same string constant, and the result range ceases to be an array. Select the smaller result range, re-edit the formula, delete the initial apostrophe, enter as an array formula. Finally, clear the contents of the cells which were part of the original array result but not the new, smaller result. If this needs to be done often, it’s a good candidate for a macro.

    Sub foo()
      Dim na As Range, oa As Range, af As String

      If Not TypeOf Selection Is Range Then Exit Sub

      Set na = Selection
      If na.Areas.Count > 1 Then Exit Sub

      On Error Resume Next
      Set oa = ActiveCell.CurrentArray
      If oa Is Nothing Then Exit Sub
      On Error GoTo 0

      af = ActiveCell.FormulaR1C1
      oa.ClearContents
      na.FormulaArray = af

    End Sub

    This macro can shrink or expand the array result range. To use it, you need to select the new result range and ensure that the active cell contains the array formula. Error checking left as an exercise.

    Moral: don’t try to do everything manually/interactively. There are many situations in which macros will be vastly more efficient than any human.

13. Very nice fzz, thank you – that will be definitely be added to my toolbox.

14. Instead of changing the formula to a string, you can also edit the array, hit Control+enter instead of CSE to make them all “normal” formulas.

15. You can download the sample workbook in Part 2

    http://www.dailydoseofexcel.com/archives/2010/09/06/building-a-self-sorting-list-part-2/

16. The [Ctrl]+[Enter] approach has one potential drawback: the resulting formulas differ from cell to cell if there were any partly or fully relative cell references. For example, if B1:B10 contained =2^(A1:A10-1), B1:B10 were selected with B1 the active cell, [F2] [Ctrl]+[Enter] produces =2^(A1:A10-1) in A1, =2^(A2:A11-1) in A2, =2^(A3:A12-1) in A3, etc. So as long as the 1st (leftmost and topmost) cell of the new array result range is the active cell when using [F2] [Ctrl]+[Enter], all’s well, but any other cell active can screw things up. Converting all cells to the same string has the advantage that it doesn’t matter which cell was active. It’s a question of which approach is more robust.

17. But why use array formulas.
    If you let use “array” names the we can use them in formulas and dont have to array enter the formula

18. @sam – if you’re going to push . . . the original article’s IF(LEN(A#)>0, test implies there could be many blank cells interspersed within the data in col A. If so, a fair amount of special processing is needed to exclude cells evaluating to “”, and some of that special processing defeats using defined names as replacements for array expressions.

19. Pingback: Dynamic Charting from disparate data

Posting code? Use <pre> tags for VBA and <code> tags for inline.