Auto selecting the unique values from a list

[graeme27uk]

I know I can do this manually but I was wondering if there was a way to do this automatically.

For example:

1, 1, 2, 4, 3, 5, 5, 3, 2, 5


should become....

1, 2, 3, 4, 5

Although in excel this would be in columns with each number in a separate cell.

[Glenn Kennedy]

This formula will also cope with blank cells. Assumes data in A1 to A30 and this Array Formula in C2, copied down.

=IFERROR(INDEX($A$2:$A$30,MATCH(0,COUNTIF($A$2:$A$30,"<"&$A$2:$A$30)-SUM(COUNTIF($A$2:$A$30,$C$1:C1)),0)),"")

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[graeme27uk]

Hurrah! Thanks very much.

[Glenn Kennedy]

You're welcome!!

[Tony Valko]

If your data really is numbers...

Data Range

	A	B	C
1	Header	------	Header
2	1		1
3	1		2
4	2		3
5	4		4
6	3		5
7	5
8	5
9	3
10	2
11	5

This formula entered in C2:

=MIN(A2:A11)

This array formula** entered in C3:

=IFERROR(1/(1/MIN(IF(A$2:A$11>C2,A$2:A$11))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.