Here's what I'm looking at. Check out the image first.
Part Number FONHAND QTYREQD Job Number DATE Allocation Job Number 2
01-3059B 5 2 5973 8/15/2018 2 5973
01-3059B 5 2 5974 8/15/2018 1 5974
01-3059B 5 2 5986 7/30/2018 2 5986
01-3059B 5 1 6393 9/6/2018 0 6393
Column 1, Part Number 01-3059B has 5 Quantity ON HAND (FONHAND Column 2). However, different quantitites are required on different job numbers. So, from Column 3, we can see that there are 7 quantities (2+2+2+1) required on 5973, 5974, 5986, 6393.
So, if there're 7 QTYRQRD while 5 FONHAND, we're short by 2.
I want you to write a custom Macro or a custom excel function which does that for me.
What I want Excel to do is, sort the QTYREQRD by DATE, and allocate them by First Come First Serve.
For example that we have here, Job Number 5986 required (QTYREQRD) 2 of these parts on July 30th, 2018, so, I want to Allocate 2 quantities first to that job. After that, I'll have just 3 remaining (while required would be 5).
So, I'll move to next DATE which is 8/15/2018 on both 5973, 5974. So, allocate 2 to 5973 and 1 to 5974 (or vice versa, doesn't matter if they've the same date). And finally allocate 0 (as we've 0 on hand after previous allocations) to Job 6393.
Final result should look like last 2 columns (Allocation & Job Number 2).
11 freelanceria on tarjonnut keskimäärin %project_bid_stats_avg_sub_26% %project_currencyDetails_sign_sub_27% tähän työhön
Hi, Had a look at the example provided. Shall deliver the Excel macro meeting your requirement within a day. Kindly confirm and provide sample file. Cheers, Arul
Hello, I am proficient with Excel and VBA and would love to help you with this project. I checked the image and I understand what you need. Please contact me for more information.
I am very experienced in Excel, macros, and VBA. I'd be glad to help you with this project. I'd like to get a bit more information about the project first and I can detail how I can be of help to you.