Forum Discussion

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

orderslist_rev3.xlsx31 KB

coltenpratt

Copper Contributor

Mar 22, 2021

mathetes So the plan once the formulas were figured out, I was going to add more columns downward for more capacity of orders. Then copy the whole chart down for a total of 52. Hence the “1” in the top left cell. The copied graph below will be “2”, then “3” and so on. This giving me the dates of each Monday of the year in the long cell at the top. As Mondays will be my harvest day.

In short meaning the order sheet being worked on is for just 1 of 52 weeks of the year.

As I’m creating this for a small business I’m just starting, each graph will not have to accommodate a mass of space for orders.

Hope this helps!

mathetes

Silver 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.

orderslist_rev3.xlsx31 KB

mathetes
Silver 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.
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