Learn how to create an Array Formula to Create Sorted Unique List in Cell. This is a huge complicated formula that requires the Excel 2016 function TEXTJOIN. There are many interesting formula elements in a single array formula to do the near impossible. Here are the topics covered (different parts that go into the final single cell formula):
(00:09) Problem Introduction
(01:43) Array of All Relative Positions & FALSEs for Unique List: using MATCH, IF and ROW functions. This is Formula Element #1.
(04:36) Unique Count: using previous formula element inside COUNT function. This is Formula Element #3.
(05:03) Array of Sequential Numbers for Unique Count: using ROW function, INDIRECT function and previous formula element. In chapter 7 of the Ctrl + Shift + Enter book, this formula element is called “Dynamic Variable-Length Array of Sequential Numbers”. Learn about the difference between using “1:” (just a row reference) and “A1:A” (a range of cells reference) in this formula element. This is Formula Element #3.
(06:58) Array of All Relative Positions: using SMALL function, Formula Element #1 and Formula Element #2. This is Formula Element #4.
(08:13) Unique List: using INDEX function, N function, IF function and Formula Element #5.
(09:57) Ranks (How Many Equal To or Above): using Formula Element #5, TRANSPOSE Function, Comparative Operator (to help sort), MMULT function (to add rows and convert 9 by 9 array into a single column array with the dimensions 9 by 1), Formula Element #3, and an exponent of zero (to convert all numbers to one). This is a trick I first learned from Domenic at the Mr Excel Message Board. This is Formula Element #6.
(16:08) Sort Relative Positions of Rank: using: MATCH function, Formula Element #3 and Formula Element #6. This is Formula Element #7.
(18:07) Sorted Unique List: using INDEX function, Formula Element #5 (Unique List), Formula Element #7 (Sort Relative Positions of Rank), N function, IF function and TEXTJOIN function. This is the final formula.
(20:30) Summary, Closing Note.