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.

 uSorted1

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 2nd 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

 

Advertisements

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

  1. Pingback: #Excel Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You

  2. Pingback: Video 00159 Create unique and sorted text list | My Spreadsheet Lab

  3. Kevin Lehrbass

    Hi David,
    I created a more low tech multi step solution. I love the one formula arrays but I often break solutions into steps when building for others.

    Cheers,
    Kevin Lehrbass

    Reply
  4. Pingback: #Excel Super Links #90 (Special Edition) – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s