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.

TJ_Item_Priority1

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.

TJ_Item_Priority2

Note that the 1st 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

 

Advertisements

2 thoughts on “List of Priority Items from Another List Using #Excel TEXTJOIN Function by David Hager

  1. Pingback: #Excel Super Links #118 – shared by David Hager | Excel For You

  2. Pingback: Creating a Priority List With #Excel 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