Can anyone make a small modification to these formulas ?

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
788
Office Version
  1. 365
Platform
  1. Windows
Hi,
i have the following:

A1
ITEM NOTYPELOCATIONQTYMAX QTY
11224PRIORITYAL4401414
11224RESERVEGL12021214
43112PRIORITYAL55126100
43112RESERVEGL440155100
55322PRIORITYAL5503823
55322RESERVEGL1105223
55322RESERVEGL11041023
55322RESERVEAL56031123
67442PRIORITYAL52121415
67442RESERVEGL1201515
80888PRIORITYBL010259
80888RESERVEGL120311
80888RESERVEHL120411
80888RESERVEGL550311
80888RESERVEGL550311
80888RESERVEGL550411
91773PRIORITYGB12091750
91773RESERVEGB12103050
91773RESERVEGB12125050
99452PRIORITYGH1202810
99452RESERVEGH1211110
99452RESERVEGH1212210

<tbody>
</tbody>
Items are always sortec by:
1: Priority / Reserve
2: Qty Smallest First

What i need is to output lines where RESERVE fully fits into the PRIORITY

Results from the above should output:

ITEM NOFROMTOQTY
43112GL4401AL551255
55322GL1105AL55032
55322GL1104AL550310
80888GL1203BL01021
80888HL1204BL01021
80888GL5503BL01021
80888GL5503BL01021
91773GB1210GB120930
99452GH1211GH12021

<tbody>
</tbody>

I have these formula currently which outputs whatever qty fits into priority. I think the one in G2 this just needs adjusting slightly to output as 0 if doesn't fully fit.

F2
Code:
=IF(B2="RESERVE",MIN(F2-SUMIFS(D$2:D$16,A$2:A$16,A2,B$2:B$16,"PRIORITY")-SUMIFS(D$1:D1,A$1:A1,A2,B$1:B1,"RESERVE"),D2),0)

G2
Code:
=IF(B2="RESERVE",MIN(F2-SUMIFS(D$2:D$16,A$2:A$16,A2,B$2:B$16,"PRIORITY")-SUMIFS(D$1:D1,A$1:A1,A2,B$1:B1,"RESERVE"),D2),0)

H2
Code:
=IF(G2>0,MAX(H$1:H1)+1)

Link to example workbook with annotations of what i need
https://drive.google.com/open?id=1CwQ2uYNJcJC0i_7V3T4cokF7Ggiq585E

Appreciate any help
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

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