Determine the grading of a product and filter accordingly

ntebaleng

New member
Joined
Jul 14, 2014
Messages
6
Reaction score
0
Points
0
Hi,I am trying to determine an approach to follow in order to grade the product.Here is a detailed example

Sheet1: This is a reference sheet used to grade the condition of the product based on their ages
Eg,If product 1's age is 8 days then it is in Excellent condition
Excellent condition(days)Good Condition(days)Bad Condition (days)Expired(days)
Product 11-1011-2020-2526 or more
Product 21-45-78-910 or more
Product 31-67-1011-1213 or more
Product 41-4040-8080-100101 or more
Product 51-3536-4545-5556 or more

Sheet 2: This is the table that gives more details about the products available at the shop.
Product Code Shelf life (days)Date of ManufactureCurrent dateAge of product (days)Volume(kg)
P101Product 1256/29/147/14/141534
P102Product 296/30/147/14/141455
P103Product 3126/26/147/14/141878
P104Product 41005/13/147/14/146212
P105Product 5556/13/147/14/143134
P106Product 3127/1/147/14/141345
P107Product 41003/3/147/14/1413356
P108Product 5556/12/147/14/143212
P109Product 1255/24/147/14/145132
P110Product 297/12/147/14/14239


The question: the user want to be able to filter the table on sheet 2 based on the condition of the products.
For example,if the user want to vies only products that have expired,the following table must appear as results:
Select Condition Expired
Product Code Shelf life (days)Date of ManufactureCurrent dateAge of productVolume(kg)
P103Product 3126/26/14 7/14/141878
P106Product 3127/1/14 7/14/141345
P107Product 41003/3/14 7/14/1413356
P109Product 1255/24/14 7/14/145132


Please advise me on how i get excel filter the data in that manner.

Thank you
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,199
Reaction score
16
Points
38
Excel Version(s)
365
In sheet1 of the attached are two versions of your Sheet1 table. The top version is your table where I have highlighted conflicting values (where, for example, for Product 1: what category does it get placed in if the product is 20 days old; 20 days appears in the Good AND bad categories. You have to decide which.
The second table is how I'd suggest you do things, have just a single number in each cell representing the thresholds only. I've highlighted those cells where I've had to guess the value you want in there. Note the shortened headers too.

On sheet2, there is also your table, with the added column H headed Condition. This column contains a formula along the lines of:
=INDEX(Sheet1!$B$9:$E$9,MATCH(F2,INDEX(Sheet1!$B$10:$E$14,MATCH($B2,Sheet1!$A$10:$A$14,0),0)))
This formula was actually derived from the cells in column M. Columns K:M (highlighted) don't need to be there anymore, they just show the steps taken to arrive at the formula; they can be deleted, there for your information only.

There is an Autofilter applied to the Condition column, but it's showing all the rows at the moment - I leave that for you.

If this is something you're happy to go along with, the formulae in column H can be made a bit more robust and easier to manage by using Named Ranges or Tables. You might also want to reduce the number of columns in this results table, since you won't need the Shelf life, the current date, nor the Age of Product since the formulae in the Condition column currently needs only to look at the Age of Product column and this can be worked out within the formula itself.
 

Attachments

  • excelguru3254-Determine-the-grading-of-a-product-and-filter-accordingly.xlsx
    11.3 KB · Views: 12
Top