Need to track and display a "Best Day" value based on category

**RBumgarner** · 09-23-2011, 10:31 AM

What I need to do is to keep track of the max value based on a category.

To elaborate:
We make 12 different products.
We can make any number of each on any given day.
My list sheet is arranged by date, category, pounds produced, and a number of other data ranges. (several thousand lines) I also have a complete list of products we can make on another sheet with the categories in column one.

I need to check if the 'pounds produced' each day is greater then the current 'best day' for the product category and if so, make it the new 'best day' value.

This will most likely be inserted into the macro I use to sort my data by manufacturing module. I have complete autonomy over this workbook so anything goes.

**JBeaucaire** · 09-23-2011, 11:14 AM

A sample workbook would make this easiest to demonstrate, but you'll need a MAX(IF) function, and that's usually an array formula. The problem with array formulas is they are memory intense. But if there are only going to be 12 of these on the sheet, that might be ok, even with a lot of data to examine.

Post up a good-sized sample workbook demonstrating data and a manual mockup of the results you're after, I'll show you from that.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

**RBumgarner** · 09-23-2011, 11:41 AM

While I can't post the actual data, I did throw together a mock up of what I'm working with

**JBeaucaire** · 09-23-2011, 12:12 PM

Your sample is missing the mockup of the sample results. What should the answers be and point out why if it's not glaringly obvious.

**RBumgarner** · 09-23-2011, 12:33 PM

Each category (I used A through I) represents a product type.
I somehow need to take the actual pounds each day and compare it to an 'all time high' which does not appear anywhere yet, but it has to be category (product type) specific..

I could filter for category and take the max value in the 'actual pounds' category, but I would have to do that for each category every day. But not all products are produced every day... some days we'll only make products C,F, and I...

This is why I'm stumped.

**JBeaucaire** · 09-24-2011, 12:33 AM

So, based on the sample sheet, the value in cell C2 would be 21158 because row 83 on sheet1 has 21158 for column D and that's the highest value.....

What would D2 be?