Search date range for quantity sum

[tavwtby]

Ok, I have a difficult one I think, at least I do not know where to begin with it. Attached is a spreadsheet that I have a master list of part numbers, with their related AADQ's(Adjusted Annual Demand Qty) in the adjacent column. Then in the respective columns after, there are order #'s, order date, part number of the order, and the qty of the order. This is my dilemma, I need to search through the orders to find any part number that has an ordered qty equal to, or greater than 50% of the AADQ of that part number, and, within a months time period. For example, if there were a part number with an AADQ of 10, and there were multiple orders who's qty's are 5 or more, and the orders all fall within a 31 day period, then that part number is entered into the green column.
I have highlighted, in orange, ones that I have done manually, and entered the part number in the green column. If a part number, within a one month period, does not equal or is greater than 50% of the AADQ, then N/A is entered. I have noticed while pouring over all these part numbers, that there are some that are not in the master list, so I am not sure how that will work in any formula. Also, being that there are multiple entries of each part number, perhaps the first step would be to consolidate the part number and add the qty., but needing the date range, I don't know if that possible. I am prepared to do this manually, if there is nothing formula or VBA wise, that can be done, but it would take me weeks and would greatly appreciate any help, as I am a novice in Excel formulas, and am learning from the help I am getting on this forum, so thanks again.

[JohnTopley]

When is the 31 period from/to?

[tavwtby]

It does not have a specific time frame, that's the thing. The dates of the orders are given, so I need to search for orders within any 31 day period, and sum the qty's of those orders for a particular part number. If you look at my attachment, I did 2 manually, and highlighted them in orange. Say there were 5 different orders between 2/10/14 and 3/10/14, and the qty's of those orders are equal to or greater than 50% of the AADQ, then I need to add that part number to the list highlighted in green. I know its a confusing one to explain, I hope I am explaining what I need correctly. With them being not in order makes it harder. What I attached is just the tip of the iceberg for that year, there are two other years with thousands of orders to scour over.

[tavwtby]

So I have changed a bit, the outcome that I am looking for. This may be a too difficult formula, and may need a macro to do this, but I am prepared to continue doing it manually. So, everything as far as looking for the ordered part number in the master list, then verifying if 50% or greater than the AADQ was ordered in any one month period. The only thing I changed is that I am now entering the order number, part number, date range, and the combined qty. in that month range. Attached is a new example, where I have done a couple manually. Hope I am explaining this correctly, if there is no solution, no worries, I appreciate any effort to try and help with this, thanks in advance.

[JohnTopley]

I don't see a new example.

[tavwtby]

Not sure why it didn't show up the first time, but it's attached now.

[JeteMc]

This is more of reply to "greatly appreciate any help" than a solution. In the attached file the pivot table looks at the PN's that have been ordered and breaks down the amounts by calendar months (I realize that isn't what was asked). The first helper column to the pivot table is the AADQ amount for that PN. Conditional formatting of the grand totals column shows which PN's were at or above the 50% AADQ threshold as those would be the only ones that could possibly have reached that threshold during a 31 day period. Conditional formatting for the monthly amounts shows where the threshold was reached during a calendar month, which would mean that it was also met during a 31 day period. Sometimes looking at the distribution of the amounts will give you a good idea of where the threshold might have been met, as is the case with PN 73802 where the threshold is not met in one month but where it was met when sales for Sept. and Oct are combined, so that it is possible that there is a 31 day period within that time span when the threshold for sales was met.
In addition there are two columns that can be used to filter the pivot table to give quicker reference to the PN's that deserve closer scrutiny. Hope that this will provide some help.
Let me know if you have any questions.

[tavwtby]

I may need to do a couple formulas for this one, or possibly try to sort the dates, because the dates are all over the place for any given part number. I am thinking of maybe a VLOOKUP, to find the matching part number in the master list, then a SUMIF, for the date range that will add the quantities within a month range,( it would be easier if it were a calendar month, but it has to be within a numerical month, i.e. 6/6/14-7/6/14), then a "=> 50% of AADQ, then a copy and paste of the order #, order date, ordered p/n, and ordered qty., I simply just don't have the excel acumen to compile that formula to make it work, also, I am not an expert on VBA, which I think would be an easier option for this solution. Any suggestions? Thanks.

[tavwtby]

Thanks, this is a good start that I can work from. As I stated earlier, there are an enormous amount of orders for each calendar year, and it would be easier if it were simply a calendar month, but because it has to be a "31 day" period, there could possibly be overlapping orders that equal to =>50% of AADQ for any given part number, this is what I have been finding doing it manually. But I like this table, it will most likely be sufficient for what I need, I just need to look at it deeper and make sure I can present it this way. They really like pivot tables too, so that's a plus. Thanks again.

[JeteMc]

You're Welcome, glad that it is useful. I hope that you have a blessed day.

[tavwtby]

You do the same.

[tavwtby]

I have a question for you regarding the pivot table. I got all the rows and columns in the pivot table, and it counts all the ordered qty's, but I cannot seem to consolidate it to just calendar months, it has each day listed in the row, as you can imagine, there are a whole bunch of columns. How did you group all the order dates to get just Jan, Feb, Mar, etc., when I tried to group them I got an error message saying that there was too much data. Thanks again in advanced.

[JeteMc]

I right clicked on the first date, 6/10/2014, and selected group then month. Please specify if you working with the spreadsheet that you uploaded in post #1? I am going to be away from computer for a while, will look at this more closely when I get back, unless someone else solves the issue before I get back.

[tavwtby]

Yes I am working off the #1 post upload. I will figure it out, I just haven't had much experience with pivot tables, and need to play around with it to figure out the workings, Thanks again.

[JeteMc]

May have made a difference, since the error said 'Too much data', that I only selected columns E, F and G (E8:G5383) to make the pivot table.
Let me know if you have any questions.

[tavwtby]

Yea, I'm not sure what I am missing, but when I insert the pivot table, everything looks good, but I cannot group the order dates, so I am left with a very long pivot table that, is correct, but I would prefer the compact month format, when I try to group them, it simply says "cannot group that selection". This time I just moved the data into a separate workbook. Not sure what I am missing here. Thanks again for any help.

[JeteMc]

When I download the file attached to post #1 > select E8:G5383 > insert a pivot table > existing worksheet > location: Sheet1!M9 > Ordered P/N's to Row Labels > Order Date to Column Labels > right click N10 > select Group > select Month it changes from 6/10/2014 to Jun.
Only suggestion that I have off the top of my head, from an experience earlier today as a matter of fact, is to restart the computer.
Hope that works.

[tavwtby]

I figured out what was wrong. I believe it may be a bug, but if I put the dates in the row label fields, and grouped them there, then I was able to move it to the column. I tried everything I could think of, until I found a post of someone doing this, and it worked. So, thanks again for your help.

[JeteMc]

I'll have to remember the grouping as row labels and moving to column headers remedy, that's clever.

[tavwtby]

Sorry to continue with this pivot table business, but I am having issues with the table that you put in with filters next to the pivot table. The grand total one works fine, but when I try to create the month column, with the "OR" statement, I put everything side by side and even tried to copy the formula and paste it, and it still returns a "VALUE#" error. From what I remember it means that basivally you have a number formatting issue. I have already filled the blanks of the pivot with 0's, and selected all cells involved in the OR formula, and it does not work, I am stumped. thanks

[JeteMc]

The formula that populates the Month column is an array entered formula, meaning that after pasting the formula in the first cell, X11 in the file I attached, you would need to press the Ctrl, Shift and Enter keys simultaneously in order to activate the formula. When you do that you'll see Excel put the brackets around the formula, trying to type them in won't work. After the formula is activated you can double click the fill handle on that cell to have it copy down.
Let me know if you have any questions.