List of Priority Items from Another List Using #Excel TEXTJOIN Function by David Hager

 

I mentioned in the previous article:

https://dhexcel1.wordpress.com/2017/08/03/creating-a-set-of-custom-instructions-using-the-excel-textjoin-function-by-david-hager/

that there were variations on the theme for returning items to a string based on a specified criteria. The variation I present here is reorder items from a list based on the selected priority. In the following figure, there is a list with a column containing items and a column containing priority selection of those items.

The goal is to create a string that orders the items based on the priority selections in the adjacent column.

Although the title of this article implies that TEXTJOIN is an important part of this technique, the heart of it is to be found in the following forrmula.

=INDEX(Item,N(IF(1,MATCH(ROW(INDIRECT(“1:”&COUNT(Priority))),Priority,0))))

The ability of the INDEX function to return an array of items is explained in this article.

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

If that formula is the heart, then this part of the formula is the magic.

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

which evaluates to {5;7;10;2;3}, the positions of the priorities from 1 to 5. The rest of the master formula coerces the INDEX function to return the desired items in the desired order. That array is used in this TEXJOIN formula to obtain the desired string.

=TEXTJOIN(CHAR(10),,INDEX(Item,N(IF(1,MATCH(ROW(INDIRECT(“1:”&COUNT(Priority))),Priority,0)))))

The result is shown below.

Note that the 1^st argument of this formula is the delimiter CHAR(10). If desired, you can use a space delimiter to make a paragragh form of the string.

The file can be downloaded here.

TJ_Item_Priority