Results 1 to 3 of 3

Thread: Determine the grading of a product and filter accordingly

  1. #1

    Determine the grading of a product and filter accordingly



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 1 1-10 11-20 20-25 26 or more
    Product 2 1-4 5-7 8-9 10 or more
    Product 3 1-6 7-10 11-12 13 or more
    Product 4 1-40 40-80 80-100 101 or more
    Product 5 1-35 36-45 45-55 56 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 Manufacture Current date Age of product (days) Volume(kg)
    P101 Product 1 25 6/29/14 7/14/14 15 34
    P102 Product 2 9 6/30/14 7/14/14 14 55
    P103 Product 3 12 6/26/14 7/14/14 18 78
    P104 Product 4 100 5/13/14 7/14/14 62 12
    P105 Product 5 55 6/13/14 7/14/14 31 34
    P106 Product 3 12 7/1/14 7/14/14 13 45
    P107 Product 4 100 3/3/14 7/14/14 133 56
    P108 Product 5 55 6/12/14 7/14/14 32 12
    P109 Product 1 25 5/24/14 7/14/14 51 32
    P110 Product 2 9 7/12/14 7/14/14 2 39


    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 Manufacture Current date Age of product Volume(kg)
    P103 Product 3 12 6/26/14 7/14/14 18 78
    P106 Product 3 12 7/1/14 7/14/14 13 45
    P107 Product 4 100 3/3/14 7/14/14 133 56
    P109 Product 1 25 5/24/14 7/14/14 51 32

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

    Thank you

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    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.

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    Groan… you've cross posted without saying so.
    Cross post: http://www.excelforum.com/excel-form...cordingly.html

    ntebaleng, have a read of this: http://www.excelguru.ca/content.php?...-cross-posters
    ExcelForum would/will want you to post links too, it's only netiquette.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •