"ignore-duplicates-and-create-new-list-of-unique-values-in-excel" explanation?

[JimmyWilliams]

Hi guys,

I just found this:
https://stackoverflow.com/questions/...alues-in-excel

I dont understand how the "MATCH(, COUNTIF($B$1:B1, $A$2:$A$25), 0)" part works.

I do understand how the COUNTIF returns an array of something like {0;1;1;0;0;...}

I don't understand how Match(,{0;1;1;0;0;...},0) becomes "1 or 2 or 4", etc, which INDEX then uses to return unique values/string.


Thanks,
Jimmy

[Glenn Kennedy]

=IFERROR(INDEX($A$2:$A$15,MATCH(0,INDEX(COUNTIF($C$1:$C1,$A$2:$A$15),0),0)),"")

Red: look in this range
Orange: for any of the values in this range

This will return a range of 0 (not found) and 1s (found)

Green: find exact matched with 0
Blue and return those values.

It returns the INDEX vslue of the first zero (not previously found value) that it encouters...

As the range in C$1:C1 increases, previously returned will be mow be "found" and will return 1s and therefore won't be counted again.

So you get a unque list of either numbers or text.

It does NOT return values in alphabetical order and falls over a bit if there are blanks IN AMONGST the values.

[JimmyWilliams]

I just figured it out,

the match occurs when it find's the first "0"

[AliGW]

Jimmy - please don't quote whole posts - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. If you are responding to the opening post, there is no need to quote it at all. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[AliGW]

Glenn - thanks for the useful explanation, which may prove useful to others.

Jimmy - I have marked the thread as solved.