Hello,
I am trying to find a way to rank items based on group, need and 12 months sales without duplicate rank places, and then distribute the quantity ordered based on the ranking. Additioanlly, if the amount to distribute is greater than the total need, that extra needs to go to the first ranking.
Attached is a picture of what I am trying to do. I did find a way to rank using CountIfs but am mostly having issues trying to distribute once it has been ranked.
The need and L12 in the actual data set is based on a SQL query so I am open to any suggestions in Excel but the solution does need to stand up to a refresh.
There are the following situations that the solution needs to address (included in the image):
Need = Distribute Amount
Need > Distribute Amount
Need < Distribute Amount
Finally, I am not used to posting so if there is any missing information I appoloigze but am happy to add.
Thannk you.
I am trying to find a way to rank items based on group, need and 12 months sales without duplicate rank places, and then distribute the quantity ordered based on the ranking. Additioanlly, if the amount to distribute is greater than the total need, that extra needs to go to the first ranking.
Attached is a picture of what I am trying to do. I did find a way to rank using CountIfs but am mostly having issues trying to distribute once it has been ranked.
The need and L12 in the actual data set is based on a SQL query so I am open to any suggestions in Excel but the solution does need to stand up to a refresh.
There are the following situations that the solution needs to address (included in the image):
Need = Distribute Amount
Need > Distribute Amount
Need < Distribute Amount
Finally, I am not used to posting so if there is any missing information I appoloigze but am happy to add.
Thannk you.
