Distribute quantity according to order price

1. Distribute quantity according to order price

Hi guys!

I need to distribute quantity by order price, that is, I have a table where I have hundreds of products in the lines and then I have customer orders in the columns (quantity and price for each order).
I've already managed to identify and order the prices from highest to lowest, and now I need to distribute the available stock among the orders, always starting at the highest price.

2. Re: Distribute quantity according to order price

This was an interesting one. I hope I got it right.

Try this in cell D12:

=LET(
OrderRnk,SORTBY(CHOOSE({1,2,3},\$D3,\$F3,\$H3),CHOOSE({1,2,3},\$E3,\$G3,\$I3),-1),
first,MIN(INDEX(OrderRnk,1),\$B3),
second,MIN(\$B3-first,INDEX(OrderRnk,2)),
third,MIN(\$B3-first-second,D3),
CHOOSE(MATCH(D3,OrderRnk,0),first,second,third))

copy it down, and then in columns F and H

3. Re: Distribute quantity according to order price

Lets hope, Greg, that the OP has a newer version of Excel. There's no LET or SORTBY in Excel 2019...

4. Re: Distribute quantity according to order price

Oh, right. Damn, I thought he had 365. I'm out!

5. Re: Distribute quantity according to order price

Hi Gregb11!

I only have one doubt: I have almost 50 orders... How do I replicate the formula for the rest?

=LET(OrderRnk;SORTBY(CHOOSE({1\2\3\4\5\6\7\8\9\10};\$P9;\$T9;\$X9;\$AB9;\$AF9;\$AJ9;\$AN9;\$AR9;\$AV9;\$AZ9);CHOOSE({1\2\3\4\5\6\7\8\9\10};\$Q9;\$U9;\$Y9;\$AC9;\$AG9;\$AK9;\$AO9;\$AS9;\$AW9;\$BA9);-1);
first;MIN(INDEX(OrderRnk;1);\$M9);
second;MIN(\$M9-first;INDEX(OrderRnk;2));
third;MIN(\$M9-first-second;AB9);
CHOOSE(MATCH(AB9;OrderRnk;0);first;second;third))

I already did this until the 10th order, but my question is the formula after which it says:

third;MIN(\$M9-first-second;P9)

I have to do this for:
Fourth
Fifth
Sixth
Seventh
Eighth
Ninth

???

Attached file with what I've done so far

6. Re: Distribute quantity according to order price

Please update your profile to show the Excel product you are using. LET & SORTBY do not exist in Excel 2019.

7. Re: Distribute quantity according to order price

Ah, having that many I noticed an issue. My formula would break down if you had duplicate order quantities at different prices, so to handle that, I had to make the formula UGLIER. Sorry about that. But this should work - I made it for 10, so you should understand how to make it for more. Maybe someone reading this can come up with a nifty formula that doesn't look so bad.
In F3, then copied to each Distribution cell:

=LET(
OrderRnk,SORTBY(CHOOSE({1,2,3,4,5,6,7,8,9,10},\$D3&","&\$E3,\$H3&","&\$I3,\$L3&","&\$M3,\$P3&","&\$Q3,\$T3&","&\$U3,\$X3&","&\$Y3,\$AB3&","&\$AC3,\$AF3&","&\$AG3,\$AJ3&","&\$AK3,\$AN3&","&\$AO3),CHOOSE({1,2,3,4,5,6,7,8,9,10},\$E3,\$I3,\$M3,\$Q3,\$U3,\$Y3,\$AC3,\$AG3,\$AK3,\$AO3),-1),
a,LEFT(OrderRnk,SEARCH(",",OrderRnk)-1),
first,MIN(INDEX(a,1),\$C3),
second,MIN(\$C3-first,INDEX(a,2)),
third,MIN(\$C3-first-second,INDEX(a,3)),
forth,MIN(\$C3-first-second-third,INDEX(a,4)),
fifth,MIN(\$C3-first-second-third-forth,INDEX(a,5)),
sixth,MIN(\$C3-first-second-third-forth-fifth,INDEX(a,6)),
seventh,MIN(\$C3-first-second-third-forth-fifth-sixth,INDEX(a,7)),
eigth,MIN(\$C3-first-second-third-forth-fifth-sixth-seventh,INDEX(a,8)),
ninth,MIN(\$C3-first-second-third-forth-fifth-sixth-seventh-eigth,INDEX(a,9)),
tenth,MIN(\$C3-first-second-third-forth-fifth-sixth-seventh-eigth-ninth,D3),
CHOOSE(MATCH(D3&","&E3,OrderRnk,0),first,second,third,forth,fifth,sixth,seventh,eigth,ninth,tenth))

8. Re: Distribute quantity according to order price

By the way, if you have the luxury, I HIGHLY recommend doing this instead. It won't matter how many orders you have.

Put the data in a table, layout like the attached.

If your data comes the way you have it, it can easily be converted to the table with this formula:

=WRAPROWS(D3:AQ3,4,"")

Then copy/paste values and convert to a table.

See attached.

9. Re: Distribute quantity according to order price

This is exactly what I need!
Many, many thanks!
As for your last proposal, it would actually be easier to create the formula (or add future orders, who knows); but it won't be easy, because I have about 1000 products.
That is, it is a list of 1000 products for 50 orders

10. Re: Distribute quantity according to order price

I don't want to abuse your wisdom, but is it possible to create a rule for when the price is the same in 2 or more orders, deliver to the one with the most quantity?
I send again the example with what I've done so far.

Plus: I can't figure out why, the 2 lowest prices always return an error (and stock is available)

Send what I've done so far

11. Re: Distribute quantity according to order price

This formula should fulfill the one with the higher quantity first if the price is the same. It should work for all prices.

=LET(OrderRnk,SORTBY(CHOOSE({1,2,3,4,5,6,7,8,9,10},\$P9&","&\$Q9,\$T9&","&\$U9,\$X9&","&\$Y9,\$AB9&","&\$AC9,\$AF9&","&\$AG9,\$AJ9&","&\$AK9,\$AN9&","&\$AO9,\$AR9&","&\$AS9,\$AV9&","&\$AW9,\$AZ9&","&\$BA9),CHOOSE({1,2,3,4,5,6,7,8,9,10},\$Q9,\$U9,\$Y9,\$AC9,\$AG9,\$AK9,\$AO9,\$AS9,\$AW9,\$BA9),-1,CHOOSE({1,2,3,4,5,6,7,8,9,10},\$P9,\$T9,\$X9,\$AB9,\$AF9,\$AJ9,\$AN9,\$AR9,\$AV9,\$AZ9),-1),
a,LEFT(OrderRnk,SEARCH(",",OrderRnk)-1),
first,MIN(INDEX(a,1),\$M9),
second,MIN(\$M9-first,INDEX(a,2)),
third,MIN(\$M9-first-second,INDEX(a,3)),
forth,MIN(\$M9-first-second-third,INDEX(a,4)),
fifth,MIN(\$M9-first-second-third-forth,INDEX(a,5)),
sixth,MIN(\$M9-first-second-third-forth-fifth,INDEX(a,6)),
seventh,MIN(\$M9-first-second-third-forth-fifth-sixth,INDEX(a,7)),
eight,MIN(\$M9-first-second-third-forth-fifth-sixth-seventh,INDEX(a,8)),
ninth,MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight,INDEX(a,9)),
tenth,MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth,P\$9),
CHOOSE(MATCH(P9&","&Q9,OrderRnk,0),first,second,third,forth,fifth,sixth,seventh,eight,ninth,tenth))

12. Re: Distribute quantity according to order price

After applying your formula to my reality, I discover that excel does not allow more than 8192 characters in formulas...
(and I have 13 510)
Is it not possible to do this in stages?

=LET(OrderRnk;SORTBY(CHOOSE({1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31\32\33\34\35\36\37\38\39\40\41\42\43\44\45};\$P9&";"&\$Q9;\$T9&";"&\$U9;\$X9&";"&\$Y9;\$AB9&";"&\$AC9;\$AF9&";"&\$AG9;\$AJ9&";"&\$AK9;\$AN9&";"&\$AO9;\$AR9&";"&\$AS9;\$AV9&";"&\$AW9;\$AZ9&";"&\$BA9;\$BD9&";"&\$BE9;\$BH9&";"&\$BI9;\$BL9&";"&\$BM9;\$BP9&";"&\$BQ9;\$BT9&";"&\$BU9;\$BX9&";"&\$BY9;\$CB9&";"&\$CC9;\$CF9&";"&\$CG9;\$CJ9&";"&\$CK9;\$CN9&";"&\$CO9;\$CR9&";"&\$CS9;\$CV9&";"&\$CW9;\$CZ9&";"&\$DA9;\$DD9&";"&\$DE9;\$DH9&";"&\$DI9;\$DL9&";"&\$DM9;\$DP9&";"&\$DQ9;\$DT9&";"&\$DU9;\$DX9&";"&\$DY9;\$EB9&";"&\$EC9;\$EF9&";"&\$EG9;\$EJ9&";"&\$EK9;\$EN9&";"&\$EO9;\$ER9&";"&\$ES9;\$EV9&";"&\$EW9;\$EZ9&";"&\$FA9;\$FD9&";"&\$FE9;\$FH9&";"&\$FI9;\$FL9&";"&\$FM9;\$FP9&";"&\$FQ9;\$FT9&";"&\$FU9;\$FX9&";"&\$FY9;\$GB9&";"&\$GC9;\$GF9&";"&\$GG9;\$GJ9&";"&\$GK9);CHOOSE({1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31\32\33\34\35\36\37\38\39\40\41\42\43\44\45};\$Q9;\$U9;\$Y9;\$AC9;\$AG9;\$AK9;\$AO9;\$AS9;\$AW9;\$BA9;be;bi;bm;bq;bu;by;cc;cg;ck;co;cs;cw;da;de;di;dm;dq;du;dy;ec;eg;ek;eo;es;ew;fa;fe;fi;fm;fq;fu;fy;gc;gg;gk);-1;CHOOSE({1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31\32\33\34\35\36\37\38\39\40\41\42\43\44\45};\$P9;\$T9;\$X9;\$AB9;\$AF9;\$AJ9;\$AN9;\$AR9;\$AV9;\$AZ9;\$BD9;\$BH9;\$BL9;\$BP9;\$BT9;\$BX9;\$CB9;\$CF9;\$CJ9;\$CN9;\$CR9;\$CV9;\$CZ9;\$DD9;\$DH9;\$DL9;\$DP9;\$DT9;\$DX9;\$EB9;\$EF9;\$EJ9;\$EN9;\$ER9;\$EV9;\$EZ9;\$FD9;\$FH9;\$FL9;\$FP9;\$FT9;\$FX9;\$GB9;\$GF9;\$GJ9);-1);
a;LEFT(OrderRnk;SEARCH(";";OrderRnk)-1);
first;MIN(INDEX(a;1);\$M9);
second;MIN(\$M9-first;INDEX(a;2));
third;MIN(\$M9-first-second;INDEX(a;3));
forth;MIN(\$M9-first-second-third;INDEX(a;4));
fifth;MIN(\$M9-first-second-third-forth;INDEX(a;5));
sixth;MIN(\$M9-first-second-third-forth-fifth;INDEX(a;6));
seventh;MIN(\$M9-first-second-third-forth-fifth-sixth;INDEX(a;7));
eight;MIN(\$M9-first-second-third-forth-fifth-sixth-seventh;INDEX(a;8));
ninth;MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight;INDEX(a;9));
tenth;MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth;INDEX(a;10));
eleventh;MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth;INDEX(a;11));
twelfth;MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh;INDEX(a;12));
thirteenth;MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth;INDEX(a;13));
fourteenth;MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth;INDEX(a;14));
fifteenth;MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth;INDEX(a;15));
sixteenth;MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth;INDEX(a;16));
seventeenth;MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth;INDEX(a;17));
eighteenth;MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth;INDEX(a;18));nineteenth;MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth;INDEX(a;19));
twentieth;MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth;INDEX(a;20));
(twenty-first);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth;INDEX(a;21));
(twenty-second);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first);INDEX(a;22));
(twenty-third);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second);INDEX(a;23));
(twenty-fourth);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third);INDEX(a;24));
(twenty-fifth);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth);INDEX(a;25));
(twenty-sixth);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth);INDEX(a;26));
(twenty-seventh);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth);INDEX(a;27));
(twenty-eight);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh);INDEX(a;28));
(twenty-ninth);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight);INDEX(a;29));
(thirtieth);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight)-(twenty-ninth);INDEX(a;30));
(thirty-first);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight)-(twenty-ninth)-(thirtieth);INDEX(a;31));
(thirty-second);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight)-(twenty-ninth)-(thirtieth)-(thirty-first);INDEX(a;32));
(thirty-third);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight)-(twenty-ninth)-(thirtieth)-(thirty-first)-(thirty-second);INDEX(a;33));
(thirty-forth);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight)-(twenty-ninth)-(thirtieth)-(thirty-first)-(thirty-second)-(thirty-third);INDEX(a;34));
(thirty-fifth);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight)-(twenty-ninth)-(thirtieth)-(thirty-first)-(thirty-second)-(thirty-third)-(thirty-forth);INDEX(a;35));
(thirty-sixth);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight)-(twenty-ninth)-(thirtieth)-(thirty-first)-(thirty-second)-(thirty-third)-(thirty-forth)-(thirty-fifth);INDEX(a;36));
(thirty-seventh);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight)-(twenty-ninth)-(thirtieth)-(thirty-first)-(thirty-second)-(thirty-third)-(thirty-forth)-(thirty-fifth)-(thirty-sixth);INDEX(a;37));
(thirty-eight);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight)-(twenty-ninth)-(thirtieth)-(thirty-first)-(thirty-second)-(thirty-third)-(thirty-forth)-(thirty-fifth)-(thirty-sixth)-(thirty-seventh);INDEX(a;38));
(thirty-ninth);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight)-(twenty-ninth)-(thirtieth)-(thirty-first)-(thirty-second)-(thirty-third)-(thirty-forth)-(thirty-fifth)-(thirty-sixth)-(thirty-seventh)-(thirty-eight);INDEX(a;39));
(fortieth);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight)-(twenty-ninth)-(thirtieth)-(thirty-first)-(thirty-second)-(thirty-third)-(thirty-forth)-(thirty-fifth)-(thirty-sixth)-(thirty-seventh)-(thirty-eight)-(thirty-ninth);INDEX(a;40));
(forty-first);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight)-(twenty-ninth)-(thirtieth)-(thirty-first)-(thirty-second)-(thirty-third)-(thirty-forth)-(thirty-fifth)-(thirty-sixth)-(thirty-seventh)-(thirty-eight)-(thirty-ninth)-(fortieth);INDEX(a;41));
(forty-second);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight)-(twenty-ninth)-(thirtieth)-(thirty-first)-(thirty-second)-(thirty-third)-(thirty-forth)-(thirty-fifth)-(thirty-sixth)-(thirty-seventh)-(thirty-eight)-(thirty-ninth)-(fortieth)-(forty-first);INDEX(a;42));
(forty-third);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight)-(twenty-ninth)-(thirtieth)-(thirty-first)-(thirty-second)-(thirty-third)-(thirty-forth)-(thirty-fifth)-(thirty-sixth)-(thirty-seventh)-(thirty-eight)-(thirty-ninth)-(fortieth)-(forty-first)-(forty-second);INDEX(a;43));
(forty-forth);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight)-(twenty-ninth)-(thirtieth)-(thirty-first)-(thirty-second)-(thirty-third)-(thirty-forth)-(thirty-fifth)-(thirty-sixth)-(thirty-seventh)-(thirty-eight)-(thirty-ninth)-(fortieth)-(forty-first)-(forty-second)-(forty-third);INDEX(a;44));
(forty-fifth);MIN(\$M9-first-second-third-forth-fifth-sixth-seventh-eight-ninth-tenth-eleventh-twelfth-thirteenth-fourteenth-fifteenth-sixteenth-seventeenth-eighteenth-nineteenth-twentieth-(twenty-first)-(twenty-second)-(twenty-third)-(twenty-fourth)-(twenty-fifth)-(twenty-sixth)-(twenty-seventh)-(twenty-eight)-(twenty-ninth)-(thirtieth)-(thirty-first)-(thirty-second)-(thirty-third)-(thirty-forth)-(thirty-fifth)-(thirty-sixth)-(thirty-seventh)-(thirty-eight)-(thirty-ninth)-(fortieth)-(forty-first)-(forty-second)-(forty-third)-(forty-forth);P\$9);
CHOOSE(MATCH(P9&";"&Q9;OrderRnk;0);first;second;third;forth;fifth;sixth;seventh;eight;ninth;tenth;eleventh;twelfth;thirteenth;fourteenth;fifteenth;sixteenth;seventeenth;eighteenth;nineteenth;twentieth;twenty-first;twenty-second;twenty-third;twenty-fourth;twenty-fifth;twenty-sixth;twenty-seventh;twenty-eight;twenty-ninth;thirtieth;thirty-first;thirty-second;thirty-third;thirty-forth;thirty-fifth;thirty-sixth;thirty-seventh;thirty-eight;thirty-ninth;fortieth;forty-first;forty-second;forty-third;forty-forth;forty-fifth))

13. Re: Distribute quantity according to order price

Is it not possible to do this in stages?
Not sure what you mean by this.

I still say the way to go is with Excel Tables. Can you attach something closer to your actual file then I think I could lay it out properly that would make this much easier.

14. Re: Distribute quantity according to order price

Hi Greg,

My difficulty is creating a table when I have a lot of chances (about 1000 products for 45 orders)
Attached is the file closest to my reality.

There are currently 1 users browsing this thread. (0 members and 1 guests)