JumboCactuar
Well-known Member
- Joined
- Nov 16, 2016
- Messages
- 788
- Office Version
- 365
- Platform
- Windows
Hi,
i have the following:
A1
<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:
<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
G2
H2
Link to example workbook with annotations of what i need
https://drive.google.com/open?id=1CwQ2uYNJcJC0i_7V3T4cokF7Ggiq585E
Appreciate any help
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>
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
https://drive.google.com/open?id=1CwQ2uYNJcJC0i_7V3T4cokF7Ggiq585E
Appreciate any help