Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am trying to extract all unique names in a list. Optimally, this would work like the FREQUENCY function, that returns an array (that can then be accessed via the INDEX function).

I know both PivotTables and the Advanced Filter can do this, but both don't update dynamically.

Is there a function I've missed?

share|improve this question
up vote 3 down vote accepted

Check out this post on sorting and de-duping a list automatically without code:

Building a self-sorting list Part 1
Building a self-sorting list Part 2

share|improve this answer
+1 Be sure to read Part 2 for the complete instructions. – Dennis Williamson Sep 17 '10 at 16:58
Thanks much! =) – Jens Sep 20 '10 at 6:17

From this page. formula 1:


Enter it in B2 and do a fill down.

From this page, formula 2:


Enter it in C2 and do a fill down. Name the range B2:B8 "BlanksRange" and the range C2:C8 "NoBlanksRange". The two ranges must have the same number of rows (they don't have to be on the same rows, though).

Enter some values in the cells from A2 through A8. Try changing them. You'll see the lists in columns B and C change dynamically.

A sample sheet:

values    formula 1    formula2
1         1            1
2         2            2
5         5            5
2         ""           7
1         ""           ""
5         ""           ""
7         7            ""

The two double quotes represent cells displaying blanks

share|improve this answer
Thanks much! I marked the answer from Sux2Lose, since it does not rely on VBA. – Jens Sep 20 '10 at 6:16
@Jens: There's no VBA in my answer. – Dennis Williamson Sep 20 '10 at 13:00
Uhm.. yeah. Stupid me, sorry. I was confused by the "You cann also you VBA to create a NoBlanks function" on the page you linked. – Jens Sep 21 '10 at 7:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .