Creating priority based on #1 earliest date & #2 Highest $

[Jamie Wagler]

Hello,

Please refer to the attached file.

Goal
What I am wanting to achieve is to assign a priority for the items listed in column A of the "COUNT" tab that correspond with the items listed in column F of the "6WeekForcastFirm" tab based on 2 criteria.

1. Smallest or nearest date to today from the "6WeekForcastFirm" column G.
2. Highest $ value "COUNT" tab column C.

This can be simply 1-50 with 1 being the greatest priority.

Issue

I began with the INDEX(Match below, but am uncertain on how to retrieve the dates as they appear on a different row from the items.

=index('6WeekForcastFirm'!$F$3:$G$5000,match(Count!$A19,'6WeekForcastFirm'!$F$3:$f$5000,0),small('6WeekForcastFirm'!$g$3:$G$5000

I am also uncertain of how to tie the two criteria together.

Thanks in advance for all of your help!

 

[Nebu]

I guess you have uploaded a wrong file
There is no Count tab , no"6WeekForcastFirm" tab .....

 

[Hui]

Jamie

When working with 2 criteria to rank there has to be some way to qualify the impact of the second variable on the first

Can you clarify what that is ?

eg If we have two criteria with scores of 90 and 20
is 80 and 30 better than that or not, what is the relationship between criteria 2 and 1?

in some areas Criteria 2 is only used to differentiate where criteria 1 is the same
eg
80 & 20 is not as good as 80 and 21 or 80 and 99 as the 21 and 99 are higher than 20

or

80 and 90 is not as good as 81 and 99 because 81 takes precedence

 

[Jamie Wagler]

Thanks for your reply, I have attached a simplistic version in this email which I hope helps.

The first criteria is the nearest date; I am dealing with a forecast looking out Min 7 days to Max 6 weeks from today.

The second criteria is highest cost per part.

For example if 2 parts are due on March 31 the part with the highest cost would be ranked higher than the other. If a part is due on March 29 and another due on the 30th and has a higher $ the part due on the 29th, the part due on the 29th would be ranked higher.

Does this help?

 

[Nebu]

Hi:

I am not sure is this what you are looking for, I could not make out much from the data you have uploaded find the attached.

Thanks