# Dynamically updating list of unique column entries in excel?

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?

-

## 2 Answers

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

-
+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:

``````=IF(COUNTIF(\$A\$2:A2,A2)=1,A2,"")
``````

Enter it in B2 and do a fill down.

From this page, formula 2:

``````=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))
``````

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

-
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