Decreasing Running List

D

dadodagrisez

New Member
Joined
Aug 9, 2023
Messages
3
Office Version
  1. 2021
Hello,

This report has been taking me hours to do every month. Here is my challenge. In each of our Warehouse locations we have a set number of Instruments that need to be distributed to a number of orders we have for that instrument. Here is what each column represents:

A: The warehouse location number
B: The type of Instrument
C: The Brand of Instrument
D & E: Are not relevant to what I am wanting to accomplish
F: The total number of that brand of instrument
G: The number of instruments that need to be built for each order.
H & I: Is where I am hoping to put the formulas that can make this report easier

Take rows 2-4 as an example. I have enough materials to build 11 of the Buffet Saxophones. I need 8 for one order, 4 for the next order, and 1 for the last order. I want to fulfill these orders by the greatest amount needed to the least. So I can fulfill the first order of 8 (cell H2). Which would leave me with 3 left (Cell I2). I can build then 3 out of 4 for the second order so 3 (cell H3) and 0 (Cell I3). Then I have none for the third order so 0 (Cell H4 and I4). As you can see, I have multiple orders per location, and I have multiple locations as well. Any help on this would be awesome.

Thanks,
 

Attachments

  • Excel help.JPG
    Excel help.JPG
    119.6 KB · Views: 5

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
dadodagrisez said:
Hello,

This report has been taking me hours to do every month. Here is my challenge. In each of our Warehouse locations we have a set number of Instruments that need to be distributed to a number of orders we have for that instrument. Here is what each column represents:

A: The warehouse location number
B: The type of Instrument
C: The Brand of Instrument
D & E: Are not relevant to what I am wanting to accomplish
F: The total number of that brand of instrument
G: The number of instruments that need to be built for each order.
H & I: Is where I am hoping to put the formulas that can make this report easier

Take rows 2-4 as an example. I have enough materials to build 11 of the Buffet Saxophones. I need 8 for one order, 4 for the next order, and 1 for the last order. I want to fulfill these orders by the greatest amount needed to the least. So I can fulfill the first order of 8 (cell H2). Which would leave me with 3 left (Cell I2). I can build then 3 out of 4 for the second order so 3 (cell H3) and 0 (Cell I3). Then I have none for the third order so 0 (Cell H4 and I4). As you can see, I have multiple orders per location, and I have multiple locations as well. Any help on this would be awesome.

Thanks,
Click to expand...
Can you provide a good representative set of data for both worksheets?

Use XL2BB so that people can use the data for testing.
 
Upvote 0
I think this will work in excel2021. It got ugly in a hurry.
MrExcelPlayground19.xlsx
ABCDEFGH
1HubInsttypeBrandDEInstBodyHubOHNeedtobuildCanBuild?
26900ASAXBuffet118Yes
36900ASAXBuffet114No
46900ASAXBuffet111Yes
56900ASAXSelmer1039No
66900ASAXSelmer1038No
76900ASAXSelmer107Yes
86900ASAXSelmer102Yes
96900ASAXSelmer102No
106900ASAXSelmer102No
116900ASAXYamaha168272No
126900ASAXYamaha168240No
136900ASAXYamaha16899Yes
146900ASAXYamaha16860Yes
156900ASAXYamaha16845No
166900ASAXYamaha1687Yes
176900ASAXYamaha1687No
186900ASAXYamaha1683No
196900ASAXYamaha1682Yes
206900ASAXYamaha1682No
216900ASAXYamaha1682No
226900ASAXYamaha1681No
236900ASAXYamaha1681No
Sheet24
Cell Formulas
RangeFormula
H2H2=LET(a,FILTER($G$2:$G2,($A$2:$A2=A2)*($B$2:$B2=B2)*($C$2:$C2=C2)),b,F2,d,SUM(a),IF(d<=b,"Yes","No"))
H3:H23H3=LET(z,($A$2:$A3=A3)*($B$2:$B3=B3)*($C$2:$C3=C3),a,FILTER($G$2:$G3,z),b,F3,d,SUM(a),f,FILTER(IF(SEQUENCE(ROWS(H$2:H2)+1)<ROWS(H$2:H2)+1,H$2:H2,"Yes")="Yes",z),g,IF(SUM(a*f)<=b,"Yes","No"),g)
 
Upvote 0
Herakles: Unfortunately, my work computer will not allow me to download the XL2BB.

James: Your formula tells me if I can fulfill the order, but it does not tell me how many instruments are remaining after fulfilling. The other part of this is that I do not care about how many order I can fill. I care about fulfilling as much of the orders as I can from largest NEED_TO_BUILD to smallest.

I have uploaded another picture to demonstrate what the answers need to be. As you can see from rows 2-4 I can build 3 out of 4 instruments so I want a 3 in column H. I can not build anymore as I have ran out of product so the rest of this section are zeros.
 

Attachments

  • Excel help.JPG
    Excel help.JPG
    116.4 KB · Views: 2
Upvote 0
I figured mine to only fill complete orders, so skip an order if it put it over the top of what could be made and check the next. I think partial orders should be much much easier than the mess I made...
 
Upvote 0
MrExcelPlayground19.xlsx
ABCDEFGH
1HubInsttypeBrandDEInstBodyHubOHNeedtobuildCanBuild?
26900ASAXBuffet1188
36900ASAXBuffet1143
46900ASAXBuffet1110
56900ASAXSelmer103910
66900ASAXSelmer10380
76900ASAXSelmer1070
86900ASAXSelmer1020
96900ASAXSelmer1020
106900ASAXSelmer1020
116900ASAXYamaha168272168
126900ASAXYamaha1682400
136900ASAXYamaha168990
146900ASAXYamaha168600
156900ASAXYamaha168450
166900ASAXYamaha16870
176900ASAXYamaha16870
186900ASAXYamaha16830
196900ASAXYamaha16820
206900ASAXYamaha16820
216900ASAXYamaha16820
226900ASAXYamaha16810
236900ASAXYamaha16810
Sheet24
Cell Formulas
RangeFormula
H2H2=MIN(F2:G2)
H3:H23H3=IF(SUMIFS(G$2:G3,A$2:A3,A3,B$2:B3,B3,C$2:C3,C3)<=F3,G3,MAX(0,F3-SUMIFS(G$2:G2,A$2:A2,A3,B$2:B2,B3,C$2:C2,C3)))
 
Upvote 1
James this is awesome I now need the next column which will show me how many are remaining after every order
 
Upvote 0
MrExcelPlayground19.xlsx
ABCDEFGHI
1HubInsttypeBrandDEInstBodyHubOHNeedtobuildCanBuild?Remaining
26900ASAXBuffet11883
36900ASAXBuffet11430
46900ASAXBuffet11100
56900ASAXSelmer1039100
66900ASAXSelmer103800
76900ASAXSelmer10700
86900ASAXSelmer10200
96900ASAXSelmer10200
106900ASAXSelmer10200
116900ASAXYamaha1682721680
126900ASAXYamaha16824000
136900ASAXYamaha1689900
146900ASAXYamaha1686000
156900ASAXYamaha1684500
166900ASAXYamaha168700
176900ASAXYamaha168700
186900ASAXYamaha168300
196900ASAXYamaha168200
206900ASAXYamaha168200
216900ASAXYamaha168200
226900ASAXYamaha168100
236900ASAXYamaha168100
Sheet24
Cell Formulas
RangeFormula
H2H2=MIN(F2:G2)
I2:I23I2=MAX(0,F2-SUMIFS(H$2:H2,$A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2))
H3:H23H3=IF(SUMIFS(G$2:G3,A$2:A3,A3,B$2:B3,B3,C$2:C3,C3)<=F3,G3,MAX(0,F3-SUMIFS(G$2:G2,A$2:A2,A3,B$2:B2,B3,C$2:C2,C3)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
2 column lookup formula to reference new data
Replies
1
Views
170
awoohaw
awoohaw
R
  • Solved
Read through XML file
Replies
1
Views
243
rjtaylor
R
S
  • Question
Neep help setting up table for plot graph
Replies
0
Views
96
supermom28115
S
J
  • Question
Cycle through loop and amend cell contents
Replies
5
Views
213
JAGIUS
J
D
  • Question
Find matching value in table, check if specific cell in same row is empty, find next if not
Replies
0
Views
207
dubyarly
D

Forum statistics

Threads
1,215,069
Messages
6,122,954
Members
449,095
Latest member
nmaske

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top