Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Priority Column based on several factors

0

I have several columns and i need the priority column to state wether the priority is high, Medium High, Medium, Low based on criteria in several of the other columns.

High

New members

Members with score 5 or higher

Or members that have score less than 5 but are high risk and have a date blank or not in 2019

Medium High

Score 2-4

No 2019 date

Medium

Score 0-1

No 2019 date

Low

score 0

2019 date

Here are the columns with some data

Date Score Risk Status Priority 10/13/2018 9 Y Retained   2/1/2019 4 N NEW     3 Y Retained   1/14/2017 6 N Retained   1/5/2016 12 Y Retained     1 N Retained   8/25/2018 0 N NEW  

Answer
Discuss

Discussion

This isn't difficult but it needs a workbook with some data. You can attach a workbook by editing your question.
Variatus (rep: 4889) Feb 22, '19 at 6:32 am
Ok i added a small sample if you could help me that would be amazing
cramos1 Mar 1, '19 at 9:30 pm
Add to Discussion

Answers

0

Please try this formula in E2 of your example and copy down.

=IF(YEAR($A2)=2019,"Low",IF(OR($C2="y",$D2="new"),"High",INDEX({"Low","Medium","Medium High","High"},MATCH($B2,{0,1,2,5},1))))

The following logic is applied.

  1. If the date's year = 2019 the priority is "Low"
  2. If the (Status = New) or (Risk = Y), the priority is "High"
  3. If neither of the above applies, priority is decided by Score:
    =>5 = "High"
    =>2 = "Medium High"
    =>1 = "Medium"
    =>0   ="Low"
    This schedule is laid out in the MATCH part of the formula, i.e.MATCH($B2,{0,1,2,5},1)) and may be modified there. It isn't possible to assign 0 score to both "Medium" and "Low".

Here is a variation on the above which arrives at the same result in a different way.

=INDEX({"Low","Medium","Medium High","High"},MATCH(IF(YEAR($A2)=2019,0,IF(OR($C2="y",$D2="new"),100,$B2)),{0,1,2,5},1))

In this variation the priority is purely decided by the score but the score itself is modified.

  1. If the date's year = 2019 the score = 0
  2. If the (Status = New) or (Risk = Y), the score = 100
  3. If neither of the above applies, Score is taken from column B
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login