Results 1 to 4 of 4

Thread: Finding the Min and assigning to that category

  1. #1
    Board Regular
    Join Date
    Jun 2017
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Finding the Min and assigning to that category

    I have a spreadsheet with 2 separate sheets set up as follows

    List
    Department Shift State
    Home Health Nights
    Toys Nights
    Toys Days
    Sporting Goods Days
    Grocery Days
    Toys Nights
    Furniture Days
    Pets Nights

    The goal of this is to look at the summary sheet below and populate the State column with the lowest value that matches the department and shift.

    Summary sheet:
    Department Alabama Alabama Alaska Alaska Maryland Maryland Maine Maine
    Pets Days Nights Days Nights Days Nights Days Nights
    Grocery 5 6 9 4 2 34 8 6
    Garden 5 879 4 5 62 154 87 52
    Home Health 1567 79 712 3 456 78 152 12
    Cosmetics 1 2 65 79 8 4891 54 568
    Furniture 45 45 878 9 8 455 657 26
    Toys 126 489 897 7891 324 348 48 9
    Sporting Goods 87 98 7 1 56 4897 97 321

    These aren't numbers on the summary sheet, they are a countifs statement taht counts the state column (C) from the summary sheet for that specific unit and shift. For example there are a count of 456 "Maryland" in column C on the summary sheet.

    When a state is populated in the "List" Spreadsheet the formulas on the summary tab would add 1 to that department/state/Day field respectively. Therefor the next item in the list could be impacted from the previous item on the list.

    Is this possible with a formula in the State cells on the List spreadsheet?

    Thank you in advance!

  2. #2
    Board Regular RasGhul's Avatar
    Join Date
    Jul 2016
    Posts
    547
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Finding the Min and assigning to that category

    Hi Magoos,

    This looks like its working ok, Note that Pets doesn't have values so the formulas start from Grocery.

    Also I made a extra column for the Min so I could see at a glance that is working correctly;


    Here's a link to my mockup version as HTML seems to be off here so I can't post sample code...

    https://www.dropbox.com/s/1nh2x0ydpx...ball.xlsx?dl=0

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Finding the Min and assigning to that category

    Quote Originally Posted by RasGhul View Post
    .. HTML seems to be off here so I can't post sample code...
    Thread has now been moved to the correct forum (with HTML on) so it would be good if you did now post your suggestion here.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    Board Regular RasGhul's Avatar
    Join Date
    Jul 2016
    Posts
    547
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Finding the Min and assigning to that category

    @Peter_SSs thanks for moving sir..;

    Hi Magoos,

    This looks like its working ok, Note that Pets doesn't have values so the formulas start from Grocery.

    Also I made a extra column for the Min so I could see at a glance that is working correctly;

    ABCDEFGHI
    1DepartmentAlabamaAlabamaAlaskaAlaskaMarylandMarylandMaineMaine
    2PeriodDaysNightsDaysNightsDaysNightsDaysNights
    3Grocery569423486
    4Garden587945621548752
    5Home Health15677971234567815212
    6Cosmetics1265798489154568
    7Furniture45458789845565726
    8Toys1264898977891324348489
    9Sporting Goods87987156489797321

    Summary





    ABCD
    1DepartmentShiftMinState
    2CosmeticsDays1Alabama
    3CosmeticsNights2Alabama
    4FurnitureDays8Maryland
    5FurnitureNights9Alaska
    6GardenDays4Alaska
    7GardenNights5Alaska
    8GroceryDays2Maryland
    9GroceryNights4Alaska
    10Home HealthDays152Maine
    11Home HealthNights3Alaska
    12PetsDays0Alaska
    13PetsNights0Alaska
    14Sporting GoodsDays7Alaska
    15Sporting GoodsNights1Alaska
    16ToysDays48Maine
    17ToysNights9Maine

    List



    Worksheet Formulas
    CellFormula
    D2=INDEX(Summary!$B$1:$I$1,,SUMPRODUCT((Summary!$A$3:$A$9&Summary!$B$2:$I$2&Summary!$B$3:$I$9=A2&B2&C2)*(COLUMN(Summary!$B$1:$I$1)-COLUMN(Summary!$A$1))))

    Array Formulas
    CellFormula
    C2{=MIN(IF(Summary!$A$3:$A$9&Summary!$B$2:$I$2=$A2&$B2,Summary!$B$3:$I$9))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself


    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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