Selecting Data

[Big Al]

Relatively new to MS Excel and trying to find my way

Have a spreadsheet with a column of upto 100 different values, however more
often than not there may be 100 entries and only 3 or 4 differnet values
repeated throughout

Is there an Excel function that would allow me to identify all the different
values in the 100 entries ... and output these 3 or 4 values into specified
cells ???

I have used the MODE function however this returns only the 1 value and not
all those in the list

Many thanks

Alan

 

[Frank Kabel]

Hi
if your data is in A1:A100 use the following formulas:
B1:
=A1

B2: Enter the following array formula (entered with CTRL+sHIFT+ENTER):
=INDEX($A$1:$A$100,MATCH(0,COUNTIF(B$1:B1,$A$1:$A$100)+
70000*ISBLANK($A$1:$A$100),0))
and copy this formula down until the list uf unique entries is
exhausted.

If you want to prevent the error code (which is shown after no unique
entries are found) change the formula in B2 to:
=IF(ISERROR(INDEX($A$1:$A$100,MATCH(0,COUNTIF(B$1:B1,$A$1:$A$100)+
70000*ISBLANK($A$1:$A$100),0))),"",INDEX($A$1:$A$100,MATCH(0,COUNTIF(B$
1:B1,$A$1:$A$100)+ 70000*ISBLANK($A$1:$A$100),0)))
and copy this array formula down

 

[Debra Dalgleish]

Another option, if you don't need a dynamic list, is to use an Advanced
Filter to extract a list of unique values. There are instructions here:

http://www.contextures.com/xladvfilter01.html