# Can anyone make a small modification to these formulas ?

#### JumboCactuar

##### Well-known Member
Hi,
i have the following:

A1
 ITEM NO TYPE LOCATION QTY MAX QTY 11224 PRIORITY AL4401 4 14 11224 RESERVE GL1202 12 14 43112 PRIORITY AL5512 6 100 43112 RESERVE GL4401 55 100 55322 PRIORITY AL5503 8 23 55322 RESERVE GL1105 2 23 55322 RESERVE GL1104 10 23 55322 RESERVE AL5603 11 23 67442 PRIORITY AL5212 14 15 67442 RESERVE GL1201 5 15 80888 PRIORITY BL0102 5 9 80888 RESERVE GL1203 1 1 80888 RESERVE HL1204 1 1 80888 RESERVE GL5503 1 1 80888 RESERVE GL5503 1 1 80888 RESERVE GL5504 1 1 91773 PRIORITY GB1209 17 50 91773 RESERVE GB1210 30 50 91773 RESERVE GB1212 50 50 99452 PRIORITY GH1202 8 10 99452 RESERVE GH1211 1 10 99452 RESERVE GH1212 2 10

<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 NO FROM TO QTY 43112 GL4401 AL5512 55 55322 GL1105 AL5503 2 55322 GL1104 AL5503 10 80888 GL1203 BL0102 1 80888 HL1204 BL0102 1 80888 GL5503 BL0102 1 80888 GL5503 BL0102 1 91773 GB1210 GB1209 30 99452 GH1211 GH1202 1

<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

Appreciate any help

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Replies
2
Views
319

1,148,294
Messages
5,745,942
Members
423,985
Latest member
sayed manzar

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

### Which adblocker are you using?

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

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