Jan 24 2023 10:02 AM
I've got the sample table below with a list of parts/quantities and respective price quotes from 5 dummy suppliers:
What I need to accomplish here is to automatically generate a separate table that will rank all suppliers by price (from lowest to highest) for each part row.
But since this is a dynamic sheet that may grow either in number of quoted items and/or suppliers, I need this formula 2D dynamic that "spills" the results down the list of items and across the ranked suppliers for each item.
This is something I was able to accomplish using a 3-step approach detailed on the screenshot below:
Each step is detailed on the blue, orange and yellow sections above.
The yellow part is the result I want to get, and in both blue and yellow I managed to do so by inputting a formula in one cell only (I3 and U3 respectively) and Excel spill the results down and across for me - I won't bother with the details of both formulas...
The middle orange part, the 2nd step is the one I'm having trouble with since MATCH in itself works only with one-dimensional arrays, as per the screenshot below:
In order for this formula to work I'd always need to copy/paste down every time new items are added to the quote table, as it only covers growth on number of suppliers across.
I've tried a couple different approaches using RANK and BYROW but to no avail, any help is appreciated here...
I also attached the Excel file that produces the above screenshots.
Thanks in advance!
Jan 24 2023 10:34 AM
Solution@leolapa I formatted your data as a Table and then used this:
=LET(h,DROP(PartTable[#Headers],,2),
s,SEQUENCE(1,COLUMNS(PartTable)-2,3),
REDUCE(s-2,SEQUENCE(ROWS(PartTable)),
LAMBDA(p,r, VSTACK(p,SORTBY(h,INDEX(PartTable,r,s))))))
see attached
Jan 24 2023 11:29 AM
Jan 24 2023 10:34 AM
Solution@leolapa I formatted your data as a Table and then used this:
=LET(h,DROP(PartTable[#Headers],,2),
s,SEQUENCE(1,COLUMNS(PartTable)-2,3),
REDUCE(s-2,SEQUENCE(ROWS(PartTable)),
LAMBDA(p,r, VSTACK(p,SORTBY(h,INDEX(PartTable,r,s))))))
see attached