Using TEXTJOIN: Generating a Sorted Unique Array in #Excel using Only Formulas by David Hager

I like to review articles from various Excel sites to get ideas for new creations and to use the “formula

technology” displayed there. I was reading an article from Oscar’s great Excel site at

http://www.get-digital-help.com/2009/05/25/create-a-drop-down-list-containing-only-unique-distinct-alphabetically-sorted-text-values-using-excel-array-formula/

and I decided to try and extend his work by creating a sorted unique array derived from a list. I wanted this to use with one of my favorite Excel functions (TEXTJOIN). The final result is shown in the following figure.

 

Now, to how this was made. The following formula was created by Oscar (and slightly modified by me). Is uses COUNTIF to determine the number of array items that are “greater” than the rest of the items (i.e. A>B).

UniqueArr1=IF(MATCH(COUNTIF(List,”>”&List)+1,COUNTIF(List,”>”&List)+1,0)=ROW(INDIRECT(“1:”&COUNTA(List))),COUNTIF(List,”>”&List)+1,””)

{1;21;26;16;12;””;14;19;7;””;17;25;””;10;3;29;13;””;9;8;6;18;23;24;””;””;””;””;22;””}

Now, the challenge was to create an array of positions from UniqueArr1 corresponding to the largest to smallest numbers in UniqueArr1. If you locate the largest number in UniqueArr1(29), it is in position 16 in the array. Likewise, the 2^nd largest number in UniqueArr1(26), is in position 3. This was accomplished by using the following formula (although I admit that it took a while to figure this out).

=MATCH(LARGE(UniqueArr1,ROW(INDIRECT(“1:”&COUNT(UniqueArr1)))),UniqueArr1,0)

It is important to note that the ROW array is dimensioned with COUNT(UniqueArr1), since the elements of UniqueArr1 greater than COUNT are null values. As you can see, the resulting array is correctly dimensioned.

{16;3;12;24;23;29;2;8;22;11;4;7;17;5;14;19;20;9;21;15;1}

Now, we have an array with the sorted positions in the correct order and that array can be used return itemd from the List array. A few years ago, it was thought that the INDEX function could not return an array of items, but then a great solution to this appeared at the excelxor.com site. See:

https://excelxor.com/2014/09/05/index-returning-an-array-of-values/

The resulting formula is shown below.

uSortedArr=INDEX(List,N(IF(1,MATCH(LARGE(UniqueArr1,ROW(INDIRECT(“1:”&COUNT(UniqueArr1)))),UniqueArr1,0))))

The resultant array is:

{“Bovey”;”Bullen”;”Cronquist”;”Dalgleish”;”Devenshire”;”Duggirala”;”Green”;”Hager”;”Hodge”;”Jelen”;”Kusleika”;”Manville”;”McRitchie”;”Mehta”;”Ogilvy”;”Pearson”;”Peltier”;”Pieterse”;”Puls”;”Rech”;”Umlas”}

This array can now be converted back to a string by using the TEXTJOIN function. The use of the delimiter CHAR(10) allows the result to be displayed as shown in the figure earlier if the cell is formatted with word wrap as true and is merged with lower cells. The formula (in cell K1 in the example) is:

=TEXTJOIN(CHAR(10),,uSortedArr)

You can download the file from this link. Remember, you must have a correct version of Excel 2016 for the TEXTJOIN function to work.

UniqueSorted