Finding the Min and assigning to that category

Magoosball

Board Regular
Joined
Jun 4, 2017
Messages
70
Office Version
  1. 365
I have a spreadsheet with 2 separate sheets set up as follows

List
Department
ShiftState
Home HealthNights
ToysNights
ToysDays
Sporting GoodsDays
GroceryDays
ToysNights
FurnitureDays
PetsNights

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
AlabamaAlabamaAlaskaAlaskaMarylandMarylandMaineMaine
Pets
DaysNightsDaysNightsDaysNightsDaysNights
Grocery569423486
Garden587945621548752
Home Health15677971234567815212
Cosmetics1265798489154568
Furniture45458789845565726
Toys1264898977891324348489
Sporting Goods87987156489797321

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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Upvote 0
[MENTION]Peter_SSs thanks for moving sir..[/MENTION]

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;


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



Book1
ABCD
1DepartmentShiftMinState
2CosmeticsDays1Alabama
3CosmeticsNights2Alabama
4FurnitureDays8Maryland
5FurnitureNights9Alaska
6GardenDays4Alaska
7GardenNights5Alaska
8GroceryDays2Maryland
9GroceryNights4Alaska
10Home HealthDays152Maine
11Home HealthNights3Alaska
12PetsDays0Alaska
13PetsNights0Alaska
14Sporting GoodsDays7Alaska
15Sporting GoodsNights1Alaska
16ToysDays48Maine
17ToysNights9Maine
List
Cell Formulas
RangeFormula
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))))
C2{=MIN(IF(Summary!$A$3:$A$9&Summary!$B$2:$I$2=$A2&$B2,Summary!$B$3:$I$9))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top