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

TJ_Item_Priority_List1

You can download the example file here.

TJ_Item_Priority_List

Advertisements

2 thoughts on “Creating a Priority List With #Excel by David Hager

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

  2. Mike Benstead

    What you have described here is a ranking system requiring no manual sorting.
    Consider utilising this with calendar dates also – very powerful.

    Reply

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