Distribute total value on mutliple columns based on ranks and maximal accepted quantity

Hi all,

This one is really tricky. I need to relocate some total (relocation) values to columns based on given ranks. But tricky part is that you should not relocate to columns above their max accepted value. For example: formula finds column with rank 1, it fulfills it max accepted quantity first, after it finds columns with rank 2, 3, 4, 5 to fulfill their needs, until it consumes total (relocation) value. So, the ranks, accepted quantaties per column (company) and Total Relocation value are given values and conditions. I must calculate relocation values per company based on these conditions in example. I marked with yellow color where formulas should go. Excel example in attachment.

example_ss1.png

I don't know what to say. This works perfectly. I hope it will be helpful for someone else also, I've been struggling with this for a long time.

Thanks and excellent job Glenn!

One way:

=IF($J3-SUMPRODUCT(($B3:$E3<B3)*$F3:$I3)>0,MIN(F3,$J3-SUMPRODUCT(($B3:$E3<B3)*$F3:$I3)),"")

Originally Posted by Phuocam

One way:

=IF($J3-SUMPRODUCT(($B3:$E3<B3)*$F3:$I3)>0,MIN(F3,$J3-SUMPRODUCT(($B3:$E3<B3)*$F3:$I3)),"")

Also works nice. This is shorter formula. Less code, it should be faster then previous one. Am I right?

I'm using data of 10-15 columns (companies), and over 400.000 rows.

If the data has 15 columns and more than 400,000 rows, using VBA would probably be better.