### 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.

## Leave a Reply

You must be logged in to post a comment.