15 thoughts on “Building a self-sorting list (Part 2)”

1. Michael,

   A very nice solution.

   Can you post the final workbook so that we can see the final formulae?

   This is exactly what I need to sort out a big list on an ongoin basis, and will prove very helpful.

2. Cool tricks to de-duplicated and collapse to unique values.

   I have one question. In column E you only check for one duplicate value. I currently have a dataset where a name occurs on more than two occasions. Sometimes three up to six times.

   Do you have a tip to check for unique names which occur n-times in a dataset?

3. I’m not sure if this is correct, but I seem to recall that a name is evaluated every time it is being called.
   If so, using the name count_BB does not mean you only do the count once.

   For example: If I put a call to a UDF in a name, each call to that name triggers a call to the UDF.

4. Alan – Thank you. I’ll forward the spreadsheet to Dick with a request that he add it to this thread. He rightly takes serious regard over what gets posted here. The formulas used were copied right out of the applicable cells into the tables. I tried to make sure no curly quotes got substituted for straight ones. You should be able to copy them directly.

   Jan Karel – Ooo. Didn’t know that. Well, then, it’s a fine example of a named formula instead of a named range. :roll:

   …mrt

5. Hi Ivo –

   The example dataset doesn’t show it (the spreadsheet will) but once the names are sorted (in Part 1) they all (1 thru many) appear grouped together in Column D. The formula in E2 will see that the 2nd is like the first and put empty text, and then the third is like the second and put empty text, and the fourth is like the third, etc. until a name comes along not like the one above it in Column D. This gets moved over, and the second gets compared etc.

   That little formula in E2, filled down, does all you need.

   …mrt

6. If you use COUNTIF(Range,Range), the approach is necessarily no better than O(N^2). The Sort method of the range class uses QuickSort, which is O(N log(N)). If efficiency is a goal, using event macros would be more efficient. While it may be possible the whittle away at the inefficiency of formula approaches, they’ll never come close to the efficiency of macro approaches.

7. To be honest, the simplest way would be to use a pivot table:

   1) insert a header row at the top of the list called “Name”
   2) create a pivot table off that plus any spare cells beloew the range you want for additional names at a later stage
   3) in the pivot table, drop in name as the row. This will automatically de-dupe and sort the list by name, really really quickly
   4) if you add more names to the list, just refresh the pivot table

   As an added bonus, it’ll even show you the number of occurrences of each name if you then drag the “Name” field in to the data items area as it’ll default to counting them if they are text fields.

8. Pivot tables aren’t automatic. Pivot tables don’t automatically change when source data changes. If automation is a key requirement, pivot tables aren’t the answer.

9. fzz – you stated in your previous post that the Sort method and VBA automation was the answer, and I agree that a Sort plus an Advanced Filter on unique records would do the necessary work. Surely, however, using VBA to automate the refresh of a PivotCache when the range changes is an equally valid approach in that case? The additional benefits are that you get the sorting and duplication bundled together rather than a 2 step approach, and also the ability to show the count of the occurrences if you require that (and even sort by that rather than by name etc.)

10. To be clear – you’d only ever need to do steps 1-3 of the original approach once. Once the table is built you can call a Pivotcaches.Refresh from the Worksheets_Change method which would then automatically refresh the list as originally required.

11. If all you want to do is sort a range, pivot tables are overkill. If you want to sort and filter, then pivot tables have their uses.

12. I vote for Nick – Pivot table with event based update of the pivot cache is not overkill – it’s ‘just-right’ kill. I use this all the time.

13. Couldn’t you avoid part 2 of this post by including a FALSE in the match formula?

    In D1 – =if(row()<=count(B:B),index(A:A,match(C1,B:B,FALSE),1),””)

14. Hi Bryan –

    The MATCH() third arguments are 1, 0, -1. Never False. 1 would work, I prefer 0.

    …mrt

15. I know this is very late to the party, but I just found this while looking for a similar solution. I noticed though that when I have many duplicates that some can make their way through to the final list so I made a simple change in E2 to make the formula

    =(COUNTIF($E$1:$E1,D2)<1,D2,"")

    This way it will count all dups above the current value that are already in the column and exclude all but the first occurrence.

---

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