Help identifying a function

  • Thread starter Thread starter ianripping
  • Start date Start date
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?
 
Could you post how to do it? My server wont allow access to that page.
No idea why?!
 
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'
 
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
 
Back
Top