Help identifying a function

I

ianripping

I need a function that will list all the different enteries in a list.
Eg


A
A
A
A
B
B
B
B
B
C
C
C
C
D
D
D
E
E
E

The function would look at the column, identify which values come up
and list them:-

A
B
C
D
E
F

Any idea?
 
I

ianripping

Could you post how to do it? My server wont allow access to that page.
No idea why?!
 
D

Debra Dalgleish

Duplicated names are in column B, starting in cell B2.
In cell A2, enter the following formula, and copy down:
=IF(COUNTIF(B$1:B2,B2)=1,MAX(A$1:A1)+1,"")

NameCount is a dynamic range with the following formula:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

NameList is a dynamic range with the following formula:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2)

In the cell where you'd like the unique list to start, enter:
=IF(MAX(NameCount)<ROW(1:1),"",VLOOKUP(ROW(1:1),NameList,2))
and copy down as far as necessary.

Or, if you don't need a function, but just need a unique list, you can
use Data>Filter>Advanced Filter, and check the box for 'unique values only'
 
P

Peo Sjoblom

Here's another method using one formula

=INDEX($B$2:$B$50,MATCH(0,COUNTIF($C$1:C1,$B$2:$B$50),0))

entered with ctrl + shift & enter

where C1 is the cell above the cell where you put the formula (so if you put
it in F2 use COUNTIF($F$1:F1,$B$2:$B$50), and B2:B50 the range with
duplicicates

copy down until you get an error
 
Top