Frequency filter by date and sorted elements

[IgnBan]

I got a data table that I need to code a filter to show the results in another sheet in the same workbook in a drill down methodology to come up with a top 5 most repetitive entries.

The table consists of quality demerit from a production line and the descriptions of these are base on a predefined syntax. Each word of the sentence is storage in a separate cell (5) that at the end of the week are move and storage in an Access Db to query for future reports, but during the week are use in several different reports, like the intended analysis post here..

In sheet ?ERRORS? I need to filter the top5 most repetitive for the week and current day, but only taking in consideration the three first columns content (a cell may contain 2 words) and show the results in sheet ?FINAL? in a descending format

I have built a Pivot table to replicate what I need, but I need to do several more reports this way and I need and idea how to start.

The reason I don?t want to use the pivot tables is to ?control? the workbook in a better way. Before the workbook is released to the floor I will put passwords to protect cells, ranges, sheets, I can also hide sheets (pivot table), but I have to give the password to supervisors because I don?t want to have a call every time they make a small mistake in a protected cell. So my intent is to give password of workbook sheets but not code. The pivot tables are very easy to mess up the top 5 if unprotected someone moves one of the fields.

Thanks in advance for any replays , and I hope everybody is doing fine with no withdraw symptoms from the site been down last week, I contra arrested my by doing something productive; mowing my lawn! Yes is that time of the year here in the States agghh !

Attached WB

[IgnBan]

Does anybody know a link or article for a way do this type of filter?
If you guys notice on the Errors sheet there are some errors description without a "Subcomponent" content, that because the description of this error doesnt need the subcomponent description. On the Pivot Table these are shown as "Blank" no problem with that but how will these empty cells can be handle in a code Sub, to still count them as part of the Top5?

[IgnBan]

Does any body have a example code or link of how to do the first part of this project?

I just need to start with the weekly filter so I can have an idea how to do the daily.


Any input is greatly appreciated in advance!

[lucas]

IgnBan, Is this the part you need help with first?

In sheet “ERRORS” I need to filter the top5 most repetitive for the week and current day, but only taking in consideration the three first columns content (a cell may contain 2 words)

So you need a rank of the rows based on what criteria in the three columns?

[IgnBan]

Lucas, thanks for anwering my post.

Yes I need to rank the sheet "ERRORS" colums error,component and subcomponent brining the number of occurances to build the Weekly Top5 most repetitive. The pivot table shows the way I want to filter the "ERROS" table, and on the Daily Top5 need colum "C" date take in consideration for the filter starting with the current day (in the sample woork book will be the most current day of data entered).

Thanks again Lucas, and stay away from rising water, I'm watching the news and things are getting bad in some places.

Stay in higher ground and dry!

[IgnBan]

Moderator or Administrator can you please close this tread, I need to researched it somewhere else.


Thanks.