Finding Unique Values in a Range

[phelbin]

I need helping finding the unique values in a range.

I'm using this formula to find the first value

{=INDEX(Y3:Y107,MATCH(TRUE,Z3:Z107>3,0))}

And this gives me the last value

{=INDEX(Y3:Y107,MATCH(TRUE,Z3:Z107>3,1))}

But the flaw in the logic is that it only gives me first and last. If the unique values are in the middle of the range, it doesn't give me the information I need. In this case, my data set has three different values. How can I find the three different values in the range?

[azumi]

Please try this using array formula

[phelbin]

Perfect! Thanks so much, Azumi.

[phelbin]

Actually...I need to take it one step further.

I'm trying to find the unique values in column A, but they're depending on Column B. For instance, count all unique values in A when column B is equal to 6.

How can this array formula account for that?

{=IFERROR(INDEX($A$3:$A$107, MATCH(0,COUNTIF($D$2:D2,$A$3:$A$107),0)),"")}

[azumi]

Its different formula when you have condition....

[phelbin]

Azumi, it won't let me download the sample file. Can you please post the formula?

[azumi]

Try this:

=IFERROR(INDEX($A$3:$A$107,SMALL(IF(FREQUENCY(IFERROR(MATCH(IF($B$3:$B$107=6,$B$3:$B$107),$B$3:$B$107,0),""),ROW($B$3:$B$107)-ROW($B$3)+1),ROW($B$3:$B$107)-ROW($B$3)+1),ROWS($A$1:A1))),"")

and press CTRL+SHIFT+ENTER button all together (array formula), and then you can copied down until blanks.....

[phelbin]

The formula works, but I can't seem to copy it down. Am I doing something wrong?

[phelbin]

Still have the same problem. The last formula suggested works, but it won't copy down. Any ideas?

=IFERROR(INDEX($A$3:$A$107,SMALL(IF(FREQUENCY(IFERROR(MATCH(IF($B$3:$B$107=6,$B$3:$B$107),$B$3:$B$107,0),""),ROW($B$3:$B$107)-ROW($B$3)+1),ROW($B$3:$B$107)-ROW($B$3)+1),ROWS($A$1:A1))),"")

[azumi]

Sorry revised for formula:

=IFERROR(INDEX($A$1:$A$750,SMALL(IF(FREQUENCY(IF($B$1:$B$750=6,MATCH("~"&$A$1:$A$750&"",$A$1:$A$750&"",0)),ROW($A$1:$A$750)-ROW($A$1)+1),ROW($A$1:$A$750)-ROW($A$1)+1),ROWS($A$1:A1))),"")

or see this:

https://www.dropbox.com/s/2mvl6cn09y...2017.xlsx?dl=0


array formula entered

[phelbin]

That's it! Thanks so much!!