HomeОбразованиеRelated VideosMore From: ExcelIsFun

Excel Magic Trick 1040: Formula To Extract & Sort A Unique List Of Numbers, No Empty Cells Or Text

77 ratings | 17326 views
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to use a helper cell and an array formula to extract a sorted unique list of numbers from a column filled with duplicate numbers. See two methods: 1. Method for Excel 2010 or later: FREQUENCY, AGGREGATE, ROWS, IF, SUM functions 2. Method for Excel 2007 or earlier: COUNTIF, SUMPRODUCT, LARGE, IF, ROWS
Html code for embedding videos on your blog
Text Comments (24)
Ar Apa-ap (9 months ago)
Awesome! I have other way to sort unique record using aggregate formula " =IF(D11="","",IFERROR(INDEX($B$1:$B$20,AGGREGATE(15,6,ROW($B$4:$B$20)/((LEN($B$4:$B$20)>0)*(COUNTIF($B$4:$B$20,"<"&$B$4:$B$20)-COUNTIF($B$4:$B$20,"<="&OFFSET(D12,-1,0,1,1))=0)),1)),""))" wher in column B is the data and in column C is the sorted unique records in ascending order.
Sam Miller (1 year ago)
How would you modify the formulas to handle dynamic ranges/lists without error?
Sam Miller (1 year ago)
I like =SUM(--(FREQUENCY(List,List)>0)) for counting unique values as it doesn't require ctrl+shift+enter.
Seymore Bizz (5 years ago)
Ok, Thanks again. I appreciate your help. I use your resources almost exclusively, and I'd buy your book just for support, but I just can't get it off MrExcel for whatever reason. Thank You
ExcelIsFun (5 years ago)
I am not sure.
Seymore Bizz (5 years ago)
One question, is there a way given your usual constructions to have a sorted unique list of 1,2,3,22,33,A,B,C from a list of FALSE,FALSE,1,33,2,A,3,C,22,B I'm not trying to circumvent any of the processes you have in place, just having trouble getting a solution
Seymore Bizz (5 years ago)
Its all good thanks for your help I'll see if I can try something else.
ExcelIsFun (5 years ago)
mrexcel. com sells the e-books.
Seymore Bizz (5 years ago)
I tried to buy your book but there's no digital copy on amazon....or site I have easy access to
ExcelIsFun (5 years ago)
Such a great tip, Leonid!!!
ExcelIsFun (5 years ago)
I tried this: =SUM(SIGN(FREQUENCY(Values,Values))) and =SUM(IF(FREQUENCY(Values,Values),1)) and =SUM(IF(FREQUENCY(A2:A42,A2:A42),1)) and other formula for similar examples like: =SUM(IF(FREQUENCY(D6:D25,D6:D25),1)) and they all seem to be working. And it appears that it is not a result of implicit intersection. Where did you learn this?
ExcelIsFun (5 years ago)
That is a great trick! I have never used this particular formula without Ctrl + Shift + Enter. It is strange, in the book I wrote I note that array functions (like FREQUENCY, MODE.MULT and others) can be housed in other functions when calculating a single answer and the formula will not require Ctrl + Shift + Enter. But what is so bafling about the examples you gave is that the array is in the IF function and this always requires Ctrl + Shift + Enter when making an array calculation!? more...
Leonid Koyfman (5 years ago)
The SUM(IF(FREQUENCY(MyArray,MyArray),1)) works for me without CtrlShiftEnter. I prefer to avoid branching and use SIGN function instead of IF construct in cases like this: SUM(SIGN(FREQUENCY(MyArray,MyArray))) It works without CtrlShiftEnter. SIGN function is useful but not popular and underused
ExcelIsFun (5 years ago)
For back and forth dialog to get Excel solutions for this very complicated situation, try THE best excel question site: mrexcel. com/forum I have a formula that may work for your situation and it is in the book, but formulas for sorting mixed data are VERY complicated.
Seymore Bizz (5 years ago)
I need help I've been looking for a solution for a while. I want to sort a column that contains both numbers and letters and I wanted to do it with one formula no helpers. I also had to create an array looking up values given a set of other values so I have some FALSEs in that array. Would greatly appreciate your help
ExcelIsFun (5 years ago)
The best Excel question site is: mrexcel. com/forum
TheBandit94 (5 years ago)
So many questions for you all! What would be a good resource for answers. I am a visual kind of guy, this is why I watch your videos. You explain it to where people like myself can understand. Thanks!
Vitor Barreto (5 years ago)
You guys should have a statue dedicated to yourselfs! :)
ExcelIsFun (5 years ago)
No, Aladin and Domenic are regular humans having fun with Excel, just like us! They just know way more than we do! :)
ExcelStrategy (5 years ago)
Yes it's true ! To me that use of the FREQUENCY function it's new I'v always used it for statistics !!! Shame on me loooool Ps. Mike the two you mentioned are Excel aliens... not humans so we humans are at a good level in comparison loool
ExcelIsFun (5 years ago)
Revolutionary for us, but it has been used by many Excel Masters for years! I first saw it from Aladin and Domenic at the Mr Excel Message Board many years ago...
ExcelStrategy (5 years ago)
I have to repeat myself, that use of the FREQUENCY function it's revolutionary !
ExcelIsFun (5 years ago)
You are welcome, World Record Holder and True Master Of Excel, krn14242!!!
krn14242 (5 years ago)
Excellent trick using Frequency versus Countif. Thanks Mike.

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.