Can a formula somehow support this?

rudolphrc

New Member
Joined
Mar 17, 2013
Messages
24
I have a required quantity of various items, but in the list of inventory to pull from I either have the same match item or have the flexibility of substituting the item to another item that has the same capability as the required item.

Example:
D0033 from the inventory can substitute for D0022 item requirement reducing the requirement to find D0022 from another location. Item requirement - D0022 Qty 35 + D0033 QTY 32 (total requirement 67) can be supported by the entire D0033 (QTY 103 avail)

D0003 from the inventory can be substituted for D0198 item requirement reducing the requirement to find D0198 from another location.


Item Requirement Inventory Avail Item still required after pulling from inventory
Item
Sum of Qty
Item
QTY
Item
Qty
D0002
3
#N/A
#N/A
D0002
3
D0003
34
D0003
271
D0003
D0005
14
D0005
52
D0005
D0007
4
D0007
42
D0007
D0009
12
#N/A
#N/A
D0009
12
D0010
D0010
2
D0010
D0011
7
D0011
10
D0011
D0012
6
D0012
13
D0012
D0013
18
D0013
28
D0013
D0015
8
D0015
30
D0015
D0016
19
D0016
86
D0016
D0017
44
D0017
150
D0017
D0022
35
#N/A
#N/A
D0022
35
D0025
4
D0025
3
D0025
1
D0027
2
D0027
2
D0027
D0030
80
D0030
90
D0030
D0031
14
#N/A
#N/A
D0031
14
D0032
8
D0032
20
D0032
D0033
32
D0033
106
D0033
D0034
27
D0034
409
D0034
D0035
52
#N/A
#N/A
D0035
52
D0036
2
#N/A
#N/A
D0036
2
D0041
6
#N/A
#N/A
D0041
6
D0042
2
D0042
10
D0042
D0043
2
D0043
10
D0043
D0044
1
D0044
5
D0044
D0045
27
#N/A
#N/A
D0045
27
D0046
80
#N/A
#N/A
D0046
80
D0047
8
#N/A
#N/A
D0047
8
D0048
30
#N/A
#N/A
D0048
30
D0052
14
D0052
94
D0052
D0053
D0053
14
D0053
D0054
2
D0054
9
D0054
D0055
D0055
2
D0055
D0056
18
#N/A
#N/A
D0056
18
D0080
20
D0080
67
D0080
D0198
66
#N/A
#N/A
D0198
66
D0211
22
D0211
24
D0211
D0215
30
D0215
26
D0215
4
D0216
2
D0216
1
D0216
1
D0235
12
D0235
14
D0235
D0462
3
#N/A
#N/A
D0462
3
D0475
10
D0475
52
D0475
D0476
1
#N/A
#N/A
D0476
1
D0477
3
D0477
1
D0477
2
D0751
6
D0751
29
D0751
D0752
D0752
3
D0752
D0862
44
D0862
3
D0862
41
D0880
47
D0880
132
D0880
D0886
76
#N/A
#N/A
D0886
76
D0887
15
#N/A
#N/A
D0887
15
D1001
14
D1001
28
D1001
D1002
4
D1002
25
D1002
D1062
45
#N/A
#N/A
D1062
45
D1064
6
D1064
6
D1064
D1073
5
#N/A
#N/A
D1073
5
D1125
D1125
35
D1125
D1158
70
D1158
40
D1158
30
D1214
5
#N/A
#N/A
D1214
5
D7000
11
#N/A
#N/A
D7000
11

<tbody>
</tbody>
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

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
Top