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

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,MA TCH(0,COUNTIF(E$5:E5,D6
:D$10),0)),"")

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

Hope this helps!

In article ,
laurie g wrote:

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

=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)=RO W(INDIRECT("1:"&ROWS($A$2:$A$10))),ROW(INDIRECT("1 :"&ROWS($A$2:$A$10)))),ROWS($1:1))))

Adjust your range to suit
ctrlshiftenter (not just enter)
Copy all the way down

------------------
mama no teeth



"laurie g" wrote:

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

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"laurie g" wrote:
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