Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

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  
+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
add comment

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

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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