NEEDING FORMULA Please

L

laurie g

Need a formula that will list a col of unique names based on multiple entries
of the same in col D. Using Advanced filter does not suit my needs.

Col D col G
5 NAME UNIQUE LIST
6 mmm mmm
7 bbb bbb
8 mmm aaa
9 aaa
10 mmm

Col D has 100 plus entries and is always being edited

Thanks
laurie g
 
D

Domenic

Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

E6, copied down:

=INDEX(D6:D$10,MATCH(0,COUNTIF(E$5:E5,D6:D$10),0))

To trap errors, try the following instead...

=IF(OR(COUNTIF(E$5:E5,D6:D$10)=0),INDEX(D6:D$10,MATCH(0,COUNTIF(E$5:E5,D6
:D$10),0)),"")

....which also needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
N

Nobody

=IF(ISERR(SMALL(IF(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW(INDIRECT("1:"&ROWS($A$2:$A$10))),ROW(INDIRECT("1:"&ROWS($A$2:$A$10)))),ROWS($1:1))),"",INDEX($A$2:$A$10,SMALL(IF(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW(INDIRECT("1:"&ROWS($A$2:$A$10))),ROW(INDIRECT("1:"&ROWS($A$2:$A$10)))),ROWS($1:1))))

Adjust your range to suit
ctrl>shift>enter (not just enter)
Copy all the way down
 
M

Max

Another way to get it up dynamically using non-array formulas ..

In G6:
=IF(ROW(A1)>COUNT(H:H),"",INDEX(D:D,MATCH(SMALL(H:H,ROW(A1)),H:H,0)))

In H6:
=IF(D6="","",IF(COUNTIF($D$6:D6,D6)>1,"",ROW()))
(Leave H1:H5 empty)

Just select G6:H6 and copy down to cover the max expected extent of data in
col D (Hide away col H). Col G returns the list of uniques from col D, neatly
bunched at the top.
 
Top