Forum Discussion

coltenpratt's avatar
coltenpratt
Copper Contributor
Mar 21, 2021
Solved

Need help figuring an Excel formula for order fulfillment

Please see attached images of the 2 spreadsheets containing data..   Essentially what I want to do is calculate in row "T" how many trays total needed to fulfill the orders placed by customers, per...
excel
Excel on Mac
Formulas and Functions
  • mathetes's avatar
    mathetes
    Mar 23, 2021

    coltenpratt 

     

    I hope you have the most recent version of Excel. I use the recently introduced function UNIQUE in this version. Use it twice, first to produce a summary of the dollars of each customer's order; second to produce a list of the products involved in all the orders, from that the total product quantity, and from that the total number of "growing trays" needed.

     

    I added three columns (by the way, you have used the word "column" where the real word is "row" ...a vertical array of cells is a column, a horizontal array is a row)...I added three columns just to make clear the progression. I'm sure it'd be possible to consolidate all those into a single formula, but that kind of "behind the scenes magic" isn't clear to you, the user. This is therefore more maintainable by you.

     

    Let me know if this works.

     

mathetes's avatar
mathetes
Gold Contributor
Mar 23, 2021

coltenpratt 

 

I hope you have the most recent version of Excel. I use the recently introduced function UNIQUE in this version. Use it twice, first to produce a summary of the dollars of each customer's order; second to produce a list of the products involved in all the orders, from that the total product quantity, and from that the total number of "growing trays" needed.

 

I added three columns (by the way, you have used the word "column" where the real word is "row" ...a vertical array of cells is a column, a horizontal array is a row)...I added three columns just to make clear the progression. I'm sure it'd be possible to consolidate all those into a single formula, but that kind of "behind the scenes magic" isn't clear to you, the user. This is therefore more maintainable by you.

 

Let me know if this works.

 

coltenpratt's avatar
coltenpratt
Copper Contributor
Mar 23, 2021
Perfect! Everything works great. I'm glad you were able to solve this as I would likely never have achieved it, as I'm sure you've noticed I'm quite new to using excel haha. As well as I noticed to calculate the total trays needed you use the LET function which never would have crossed my mind.

I thank you very much for taking the time to help me solve this.
cheers
  • mathetes's avatar
    mathetes
    Gold Contributor
    Mar 23, 2021

    Good, I'm glad it worked for you. It was fun to do.

    I just went back and looked at that LET formula and realized in looking closer that it does nothing of value. This simpler formula does the exact same thing.

     

    =IFERROR(VLOOKUP(R6,CropData,2,0),"")

     

    So switch it out.

     

    LET is fun to use, but I got carried away. It only would have made sense had I needed the VLOOKUP to happen several times.

Resources