Search code examples
excelinventoryvba

What VBA code options are there to group data into time buckets, copy it to another sheet, and loop for multiple parts (criterion)

I have two files: File#1 contains customer with demand information. There are only three columns that matter to me:
Part# Demand Qty Demand Date The file has thousands of lines.

File#2 is my own file which has more of an MRP setup: columns are labeled with dates (weekly) For each Part: There are the following rows: Demand Incoming Inventory Net Inventory

My general idea was that I could somehow filter both files by part#, then have the code "sumif" the total demand by week, and copy it from the customer file to my file on the corresponding demand row. Then loop this for all part #s.

Part #s are constantly being added/removed in my spreadsheet(as new projects develop, and old ones phase out), so it would be ideal if the code didnt need to be maintenenced as my file is updated with new parts.

Solution

  • It seems like you could use a SUMPRODUCT formula to bring those numbers in without code. This example uses two sheets in the same workbook so you'd have to adjust slightly for different workbooks. In Sheet1 you have three ranges which I've named rngPartNum, rngDemandDate, and rngDemandQty. I used dynamic range names that expand with the data, but you could use cell references that cover a sufficient number of rows. The data looks like this

    PartNum Demand Qty  DemandDate
1           18          3/28/2011
1           6           3/30/2011
1           6           4/2/2011
2           18          3/28/2011
2           6           3/30/2011
2           6           4/2/2011
2           16          3/28/2011
3           3           3/30/2011
3           15          4/2/2011
3           9           3/28/2011
3           18          3/30/2011

    Sheet2 has a week-end date and a part number on each row.

    Part    Week       Demand
1       4/1/2011    24
1       4/8/2011    6
1       4/15/2011   0
2       4/1/2011    40
2       4/8/2011    6
2       4/15/2011   0
3       4/1/2011    30
3       4/8/2011    15
3       4/15/2011   0

    The formula in the demand column is this

    =SUMPRODUCT((rngPartNum=A2)*(rngDemandDate<=B2)*(rngDemandDate>B2-7)*(rngDemandQty))

    That sums everything in rngDemandQty where rngPartNum matches A2 AND rngDemandDate is less than or equal to B2 AND rngDemandDate is greater than a week before B2. As long as you have every part number and enough weeks on your Sheet2, the total should match Sheet1.