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

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

Hi Gregb11!

You can't imagine the precious help you've given me!
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

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))

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.

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

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

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))

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))

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.

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.
Do you think it's possible to do something about this?