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?
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
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
