Filter Top 3 and display them in another sheet

[IgnBan]

Hi everybody, been surfing the net for Forum answers for this frequency filter with no luck. This is what I got; Sheet with all the daily sales of a product metal figurines. This sales data is input daily and listed in a sheet with this description (headers) Date, Product, Color, Size, Price, and Salesman. What I want to do is filter in another sheet in same workbook the best top 3 sold as they are enter, base in the 4 firsts headers ( Date, Product, Color, Size) for the Daily Top 3, and 2nd, 3rd and 4th (Product, Color, Size) for the Weekly Top 3. Is it possible to make it filter as the data is input? Say every 10 minutes? What is the best approach an expression or VBA?
Attached a sample workbook.




Any input is greatly appreciated.

[shades]

Howdy. When does the week start? Monday?

Also, you have many different sizes. Are they all meant to be unique? (several are in the 3.5 - t 4.5 range).

[shades]

Okay, this is using only formulas and dynamic named ranges.

On Sheet2, now renamed Data, there are several dynamic named ranges:

Date

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),1)

Product:

=OFFSET(Date,0,1)

Color

=OFFSET(Date,0,2)

Size

=OFFSET(Date,0,3)

Price

=OFFSET(Date,0,4)

Then on Sheet1, I put the formula for the latest Date in cell A3

=INDEX(Data!A:A,MATCH(9.999999999999E+307,Data!A:A),1)

The work area (surrounded by gray cells, normally this would be on a separate sheet), provides the basic gathering of data. Column J lists all Products. And in K4

=SUMPRODUCT((Date=$A$3)*(Product=$J4),Price)

copied down to the number of Products.

So also with N4

=SUMPRODUCT((Date=$A$3)*(Color=$M4),Price)

copied down

and Q4

=SUMPRODUCT((Date=$A$3)*(Size=$P4),Price)

then in cell C5, there is this formula to determine largest

=LARGE(K$4:K$7,$A5)

copied down, note it references the numbers in column A, so it in C6, it will reference A6, which is the second largest.

Then in cell B5, this formula:

=INDEX($J$4:$K$7,MATCH(C5,$K$4:$K$7,FALSE),1)

Copied down. Now you have the list of the top three Products for the latest date.

HTH

[Bob Phillips]

What determines the day to check for and the week to check for?

[shades]

That was part of my dilemma as I quickly put this together. My choice was for the last date entered. But it could be a drop down with all possible dates.

And it would still need to be determined when the week begins.

Only the OP can enlighten us.

[IgnBan]

Sorry guys had to do parenting duties (son soccer game),.

Howdy. When does the week start? Monday?

OK first of all the day the week starts is Monday

Also, you have many different sizes. Are they all meant to be unique? (several are in the 3.5 - t 4.5 range).

All will be whole numbers no decimals

What determines the day to check for and the week to check for?

The current day that data is been enter determines the "Daily To 3", at the end of working day data will be move to a data base (clear “Daily Top 3” fields) so next workday the field will be empty to scan current day “Date” field and start counting for “Daily Top 3”. The “Weekly Top 3” will be an accumulative count of all days of the week, that’s the reason I don’t think we want the field “Date” to be considered. All Data will be move to a database at the end of the week.

[shades]

So, my example will be what you need for the daily. And for weekly, just take Date out of the SUMPRODUCT function, which will make it essentially SUMIF.