Excel Magic Trick 1313

Array Formula to Create Sorted Unique List in Cell. HUGE Formula. TEXTJOIN.

DESCRIPTION

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.

WORKBOOKS

Download “Excel Magic Trick 1301-1320” Excel-Magic-Trick-1301-1320.zip – Downloaded 337 times – 285 KB




Be the first to comment

Leave a Reply