comparing array - finding how many kits can be made from existing inventory

[rahulraji]

Hi all,

I have inventory stock details with 11,000 part count and I am making vehicle from this inventory. Now for one vehicle I require 1300 to 1500 parts with varying quantity on each part. there will be multiple vehicle codes

I want to create a crisp VBA where it will show me
1) how many vehicles I can make considering current inventory stock.
2) how may vehicles I can make against the plan of each VC code and which parts are shortage and how much quantity for vehicle code.

I have done some work almost but these for and for each loops are increasing my processing time heavily cause I have around 11000 rows of inventory and 1300 to 1500 parts for each vehicle code.

Requesting your help guys

 

[Strithken]

Please provide example data to work with.

 

[rahulraji]

Please provide example data to work with.

I have given sample data.. not able to upload the excel file.. component & its quantity from column J & K will change as Vehicle code gets change

 

[rahulraji]

View attachment 93363


I have given sample data.. not able to upload the excel file.. component & its quantity from column J & K will change as Vehicle code gets change

Part number | Stock

Data is in In rows of 12000

And there is Vehicle code list and its part required list..

Part number | Required quantity


Now to plot how many vehicles i can make with current stock i am trying to loop vehicle code.. where vehicle required part list need to compare with stock file.

If any part is missing from part list or having zero value then i will just skip that vehicle code and move to next vehicle code.

If required part list of vehicle code is there then it should give a count of vehicle i can make considering current stock. Also the required quantity* vehicle can be made count should get minus from curre t stock so that current stock file can be updated for calculating next vehicle code count.

View attachment 93363


I have given sample data.. not able to upload the excel file.. component & its quantity from column J & K will change as Vehicle code gets change