excel vba - 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 has information about the customer's demand. I have only three columns for me Important:
Part # Demand Quantity There are thousands of lines in the demand date file <2 p> <# P> My general idea was that I can filter both files from part # to any number, after that in the week The total demand is the code of "sumif", and the demand sheet corresponding to my file from the client file Create copy. After that loop for all the part #.

Part # S is constantly being added / removed in my spreadsheet (as new projects develop, and the phase of old ones ends), so this code will not be ideal. Is being updated with.

It seems that you can use a SUMPRODUCT to bring those numbers without the formula . This example uses two sheets in the same workbook so that you have to adjust slightly for different workbooks. In Sheet 1, you have three categories, which I named rngpartNum, rngDemandDate, and rngDemandQty. I used dynamic range names that extend with data, but you can use cell references which cover a sufficient number of rows. The data looks like this 

  part demand demand quantity DemandDate 1 18-03-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/30/2011 3 15 4/2/2011 3 9 3/28/2011 3 18 3/30/2011   
 Sheet 2 has a one-week end date and one part number on each row.  
  Sections of the Week Week 1 4/1/2011 24 1 4/8/2011 6 1 4/15/2011 2 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 Code>  
 The formula in the demand column is  
 < Code> = SUMPRODUCT ((rngPartNum = A2) * (rngDemandDate & lt; = B2) * (rngDemandDate> B2-7) * (RngDemandQty))    
 This rngDemandQty Tells everything where rngPartNum matches A2 and rngDemandDate is less than or equal to B2 and rngDemandDate is greater than one week from B2. As long as you have enough time on each part number and your sheet 2, the total should be 1 plus.

Comments

Post a Comment