Finding unique distinct values

[mrichard]

Hi all,

I have a list of names (some duplicate) in a column that I want to extract only unique names in a different column.

I found this formula (which works):

=INDEX($A$2:$A$5,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$5),0,0),0))

Is there a website that I can paste this formula and will explain it. Or can anyone here explain?

Thanks in advance.

Mark

[ChemistB]

Okay, As far as I know, there is no Excel Translator Web Engine. That would be pretty cool.

Do you understand routine INDEX/MATCH Formulas?

[ChemistB]

Let's start with
Since we are looking at a single column, the second 0 (in red) is not necessary
=INDEX($A$2:$A$5,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$5),0,0),0))
so it should only be
=INDEX($A$2:$A$5,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$5),0),0))

Next lets look at
COUNTIF($B$1:B1, $A$2:$A$5)

Normally this would just return 0 because the second argument should be a single cell and it returns a single value. However, because it is entered as the Array argument for INDEX (INDEX(COUNTIF($B$1:B1, $A$2,$A$5),0), Excel expands it to an array and returns a value for every criteria in the second argument (A2:A5) so this gives us an array of {0;0;0;0} (The contents of B1 don't match A2:A5).

So now, we have
=INDEX($A$2:$A$5,MATCH(0,INDEX({0;0;0;0},0),0))
This 0 (in red) tells Excel to take every row in the array. Normally there would be a number (1,2,3 or 4) and it would return the value in that row.
So we are now at
=INDEX($A$2:$A$5,MATCH(0,{0;0;0;0}))
So Match (0...) returns a 1 because the first term of the array is 0

Now we drag that formula down to B3 (Important that the first formula was in B2)
Now the Formula reads
=INDEX($A$2:$A$5,MATCH(0,INDEX(COUNTIF($B$1:B2,$A$2:$A$5),0,0),0))
and when it counts B1:B2 against A2:A5, it counts 1 value for A2 so we get (if there are no duplicates)
{1,0,0,0}
Lets say the value in A3 is the same as A2, it would return
{1,1,0,0}
Now when the Match searches for 0, it goes to 3 and returns A4

You probably want to wrap a IFERROR around your formula so it doesn't return errors

=IFERROR(INDEX($A$2:$A$5,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$5),0),0)),"")

[mrichard]

Thanks I will evaluate your response. Probably going to take me a while.