Excel Magic Trick 1044: Formula To Extract & Sort Unique List Of Numbers Contains Empty Cells & Text

See array formula to extract and sort a unique list of numbers from a column that contains empty cells and text:
1. Array formula that contains the functions: MATCH, ISNUMBER, IF, FREQUENCY, ROW, ROWS, LARGE.

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

Amazing Array Formula!

[excelisfun]

Yes your formula does work. I address your formula in the video and suggest that it will work for small data sets, but that for large data sets the helper cell and IF formula calculates more quickly because it does not have to run the array part of the formula in every cell like the IFERROR does. I also have a section in the Ctrl + Shift + Enter book that discusses the advantages and disadvantages with the IFERROR. Here is a video also:

Excel Magic Trick 1029.

[supertotoro]

Im not sure when I would need this formula, but now that I know that it is possible, maybe I will. More importantly, practicing making these kinds of formulas and understand them really improves ones excel skills. I rarely write a comment on your videos, but I want to say I really like them and I watch them all. Thanks from Sweden.

[excelisfun]

You are welcome! I am glad that you like the videos! If you like them, do hit the Like button on every video you watch!

[myzell]

such an amazing person to upload all of these videos, I wonder who is behind this amazing voice.

[excelisfun]

Thanks for the cool heads up on the formula challenge!

[ALEXGO]

=IFERROR(LARGE(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$15), MATCH($A$2:$A$15, $A$2:$A$15, 0)), ROW($A$2:$A$15)-ROW($A$2)+1), $A$2:$A$15), ROWS(C$5:C5)), "") THEN CTL+SHIFT+ENTER
Dear Mike
I try the formula above, it works even without the helper Unique Number Count Formula cell
Anyway Thanks for all your Videos, You are so Great!!!

[jeffweir]

Hi Mike. Just discovered this series, which is fab. Looking forward to getting your array formula book. I've recently started posting a series of very tricky formula challenges at Chandoo's forum under the 'Formula Challenge' category that might be of interest, including returning duplicate/unique/distinct arrays that can be directly processed by another formula. Been some very innovative approaches that I'm sure you'll appreciate. Google Chandoo formula challenge to find it - you'll love it.

[excelisfun]

VBA will do it. For back and forth dialog to get Excel solutions, try THE best Excel question site:

mrexcel. com/forum

[JorgeObando]

Hello Toto. Conditional Formatting won't work, but I think ExcelIsFun Magic Trick 431 (GET.CELL) will do it; search for "magic trick 431" in YouTube's Search Bar.

[tompotthast]

Hi Mike, I'm using an Index/Match array lookup to return a 'text' value at a particular row & column intersection. THE PROBLEM IS there are multiple results and instances where the formula is returning a blank because its is picking up the first intersection result which happens to be a blank cell (similar to a VLOOKUP). How do I modify the array formula so that it will ignore blank cell and return the first text value matching the lookup criteria? Thanks for your help!!! : )

[ExcelStrategy]

Probably we are near the limit of what a formula can do the next step will be antigravity ??? looool