"ignore-duplicates-and-create-new-list-of-unique-values-in-excel" explanation?
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
=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.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I just figured it out,
the match occurs when it find's the first "0"
Last edited by AliGW; 09-10-2017 at 02:46 AM. Reason: Unnecessary quotation removed.
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.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Glenn - thanks for the useful explanation, which may prove useful to others.
Jimmy - I have marked the thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks