How to extract Unique Cells

Explanation…
Formula used
=INDEX($C$2:$C$9,MATCH(0,COUNTIF($E$1:E1,$C$2:$C$9),0),1)
First of all, we will try to understand this part of the forumla..
ie.
COUNTIF($E$1:E1,$C$2:$C$9)
COUNTIF(Range,Criteria)
when entered as any array formula ..
Countif will count of each element to Criteria array (ie. AAA, BBB, CCC and so on) in Range array (is {0})
it returns an array of the same size or length as the Criteria Range
ie.
{0;0;0;0;0;0;0;0}
it is returning zeros as it has not found any match for each element of Criteria array in Range array
then Match function matches 0 in the lookup range and returns the row no. of the first match zero ie. 1
MATCH(0,{0;0;0;0;0;0;0;0},0)			returns 1
after that Index Formula, come to action…
=INDEX($C$2:$C$9,1,1)			returns "AAA"
Now drag it vertically to include other uniques..
when draged .. The sintex of the Countif formula changes a bit..
like this..
COUNTIF($E$1:E2,$C$2:$C$9)
ie.
COUNTIF({0;"AAA"},{"AAA";"BBB";"CCC";"AAA";"AAA";"BBB";"BBB";"CCC"})
	Range			Criteria
Now it will count each element of criteria array(ie. AAA, BBB, CCC and so on) in the range array(ie.0,AAA)
and returns following array..
{1;0;0;1;1;0;0;0}
Now Match function will take care of first Zero and return 2 as the row no. of first matched row
Index function will do the rest and return the cell at the intersection of 2 row and 1st column
like this..
=INDEX($C$2:$C$9,2,1)			will retun "BBB"
In this way .. When draged further down.. Countif will return the counts of matched rows and
Match function will return the row no. of first zero ie. the row no. of next unique value.. And
Index Function will return the unique cell value…
Array formulas has to be entered by using CTRL + SHIFT + ENTER
Vikas Gautam

File can be downloaded here..
Unique values.xlsx