Finding the Min and assigning to that category

[Magoosball]

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

List
[TABLE="width: 500"]
[TR]
[TD]Department
[/TD]
[TD]Shift[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]Home Health[/TD]
[TD]Nights
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Toys[/TD]
[TD]Nights[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Toys[/TD]
[TD]Days[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sporting Goods[/TD]
[TD]Days[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grocery[/TD]
[TD]Days[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Toys[/TD]
[TD]Nights[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Furniture[/TD]
[TD]Days[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pets[/TD]
[TD]Nights[/TD]
[TD][/TD]
[/TR]
[/TABLE]

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:
[TABLE="width: 500"]
[TR]
[TD]Department
[/TD]
[TD]Alabama[/TD]
[TD]Alabama[/TD]
[TD]Alaska[/TD]
[TD]Alaska[/TD]
[TD]Maryland[/TD]
[TD]Maryland[/TD]
[TD]Maine[/TD]
[TD]Maine[/TD]
[/TR]
[TR]
[TD]Pets
[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]Grocery[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]34[/TD]
[TD]8[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Garden[/TD]
[TD]5[/TD]
[TD]879[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]62[/TD]
[TD]154[/TD]
[TD]87[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]Home Health[/TD]
[TD]1567[/TD]
[TD]79[/TD]
[TD]712[/TD]
[TD]3[/TD]
[TD]456[/TD]
[TD]78[/TD]
[TD]152[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Cosmetics[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]65[/TD]
[TD]79[/TD]
[TD]8[/TD]
[TD]4891[/TD]
[TD]54[/TD]
[TD]568[/TD]
[/TR]
[TR]
[TD]Furniture[/TD]
[TD]45[/TD]
[TD]45[/TD]
[TD]878[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]455[/TD]
[TD]657[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]Toys[/TD]
[TD]126[/TD]
[TD]489[/TD]
[TD]897[/TD]
[TD]7891[/TD]
[TD]324[/TD]
[TD]348[/TD]
[TD]48[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Sporting Goods[/TD]
[TD]87[/TD]
[TD]98[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]56[/TD]
[TD]4897[/TD]
[TD]97[/TD]
[TD]321[/TD]
[/TR]
[/TABLE]

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!

 

[RasGhul]

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/1nh2x0ydp...n and assigning category_Magoosball.xlsx?dl=0

 

[Peter_SSs]

.. 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.

 

[RasGhul]

[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
	A	B	C	D	E	F	G	H	I
1	Department	Alabama	Alabama	Alaska	Alaska	Maryland	Maryland	Maine	Maine
2	Period	Days	Nights	Days	Nights	Days	Nights	Days	Nights
3	Grocery	5	6	9	4	2	34	8	6
4	Garden	5	879	4	5	62	154	87	52
5	Home Health	1567	79	712	3	456	78	152	12
6	Cosmetics	1	2	65	79	8	4891	54	568
7	Furniture	45	45	878	9	8	455	657	26
8	Toys	126	489	897	7891	324	348	48	9
9	Sporting Goods	87	98	7	1	56	4897	97	321
Summary

Book1
	A	B	C	D
1	Department	Shift	Min	State
2	Cosmetics	Days	1	Alabama
3	Cosmetics	Nights	2	Alabama
4	Furniture	Days	8	Maryland
5	Furniture	Nights	9	Alaska
6	Garden	Days	4	Alaska
7	Garden	Nights	5	Alaska
8	Grocery	Days	2	Maryland
9	Grocery	Nights	4	Alaska
10	Home Health	Days	152	Maine
11	Home Health	Nights	3	Alaska
12	Pets	Days	0	Alaska
13	Pets	Nights	0	Alaska
14	Sporting Goods	Days	7	Alaska
15	Sporting Goods	Nights	1	Alaska
16	Toys	Days	48	Maine
17	Toys	Nights	9	Maine
List

Cell Formulas
Range	Formula
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.