Dynamic: Finding the Min and assigning to that category

[Magoosball]

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

Assignment:

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

<tbody>
</tbody>

Summary:

State	Department Bucket	Product	Shift	Number
Alabama	Home Health	Shampoo	Nights	1567
Alabama	Toys	Cars	Nights	900
Maryland	Toys	Barbies	Nights	897
Wyoming	Home Health	Conditioner	Nights	1570
Maryland	Sporting Goods	Basketball	Nights	65
Maryland	Grocery	Hot Dogs	Days	5
California	Pets	Dogs	Days	98
California	Grocery	Burgers	Days	74

<tbody>
</tbody>

The number column is a count statement from the assignment tab. For example from the above data there would be 1567 rows on the assignment tab that have a state of Alabama, a shift of nights, a department of Home Health and a Product of Shampoo. Since it is a count statement, for each additional row added to the assignment tab with Alabama, Home Health, Nights, and Shampoo, the number column on the summary spreadsheet would go up by 1.

The goal of this is to fill in the state and the product columns on the Assignment spreadsheet that has the lowest Number from the summary tab. Below I have typed out a few of the expected results.

1) Home Health Nights on the assignment tab -- The first 4 rows that have this on the assignment tab will be populated with Shampoo and Alabama. This would put the count of Alabama, Home Health, Shampoo, Nights at 1571 now. Since that is now greater than 1570 the next one will be assigned to Wyoming, Home Health, Conditioner, Nights. From here on out each row the formula is dragged down to that has these values should rotate between Alabama, Home Health, Shampoo, Nights AND Wyoming, Home Health, Conditioner, Nights.

2) Toys Nights on the assignment tab -- Each row on the assignment tab will be populated with Maryland and Barbie.

3) Grocery days on the assignment tab -- the first 69 should be assigned to Maryland Hot Dogs. The 70th row of this combination would be assigned to California burgers, and for each row after they would begin to rotate.

Is it possible to create this with formulas? Any helper columns are allowed. vb is allowed if needed.
I'm struggling because I'm getting a circular reference between the 2 tabs.

Any help setting this up or the formulas to make this happen would be greatly appreciated.
Thank you so much!

 

[RasGhul]

Hi mate,

This seems to be returning the correct state & product for the lowest Summary value?

Book1
	A	B	C	D
1	Department Bucket	Shift	State	Product
2	Home Health	Nights	Alabama	Shampoo
3	Toys	Nights	Maryland	Barbies
4
5	Sporting Goods	Nights	Maryland	Basketball
6	Grocery	Days	Maryland	Hot Dogs
7	Toys	Days
8	Furniture	Days
9	Pets	Days	California	Dogs
Assignment

Cell Formulas
Range	Formula
C2	{=IFNA(INDEX(Summary!$A$2:$A$9,MATCH(MIN(IF(Summary!$B$2:$B$9&Summary!$D$2:$D$9=A2&B2,Summary!$E$2:$E$9)),Summary!$E$2:$E$9,0)),"")}
D2	{=IFNA(INDEX(Summary!$C$2:$C$9,MATCH(MIN(IF(Summary!$B$2:$B$9&Summary!$D$2:$D$9=A2&B2,Summary!$E$2:$E$9)),Summary!$E$2:$E$9,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.