Creating a Priority List With #Excel by David Hager

 

In my previous article I demonstrated a technique to show a list based on priority.

https://dhexcel1.wordpress.com/2017/08/04/list-of-priority-items-from-another-list-using-excel-textjoin-function-by-david-hager/

Since I wanted the solution to be a single cell, single formula technique, the calculation was somewhat complex and the TEXTJOIN function was used to create the string. For that technique, you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work. But, many people do not have access to that Excel version. So, I decided to publish a solution that does not require TEXTJOIN. Although this method uses several intermediate steps, in many ways it is much simpler to implement.

In the list are columns defined as Item and Priority respectively. To obtain the priority list, place consecutive numbers from 1 to 5 (per the values in Priority) in column C starting at C2. Then, put the following formula in cell D2 and fill down.

=INDEX(Item,MATCH(D2,Priority,0))

That gives the priority list in column D in cells. If you want to reproduce the text string as per the previous article, place this formula in cell F14 as shown in the figure.

=E2&CHAR(10)&E3&CHAR(10)&E4&CHAR(10)&E5&CHAR(10)&E6

You can download the example file here.

TJ_Item_Priority_List