Bin Allocation from a Lot based on set of rules

[Bala216]

Hi There,

Here is the screen shot of my problem scenario and output required....

Forum experts help is highly appreciated to solve this problem...

Thank you...

Box Number Generator Problem Description.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Box Number Generator
2
3	Technical Terms used:
4	1. Lot Number
5	2. Size
6	3. Pairs
7	4.Box Number
8
9	Scenario:
10	1. A lot number is a 5 digit unique identity number given for a group of pairs from different sizes.
11	2. A lot will have minimum 18 Pairs and maximum of 144 pairs in different sizes and multiples of 18 pairs (eg. 18,36,54,72,90,108,126,144)
12	3. Each size will have minimum zero pairs and maximum of 144 pairs and will be always multiple of 3. (eg. 0, 3, 6,9,12,15………max of 144)
13	4. Minimum size is 2.5 and increases with an increment of 0.5 and maximum would be 12.
14
15	Picotral Representation of a typical scenario:
16		Sizes / Pairs
17	Lot Number:	2.5	3	3.5	4	4.5	5	5.5	6	6.5	7	7.5	8	8.5	9	9.5	10	10.5	11	11.5	12	Total Pairs
18	12345	3		6	9	12		15		24	36	36						3				144
19	67890		15	3	3	9	12	15			3			12								72
20	78901			3	3	3	3	3	3	3	3	3	3	6								36
21
22
23	Objective:
24
25	1.When a size run is filled up for a lot number, then these sizes has to be split/ grouped into sum of 12 pairs.
26	2. Each group of pairs (called Box ) will be assigned a box number starting from 1 to maximum of 12.
27	3. A full box will have maximum of 12 pairs and minmum of 3 pairs and always be either 3 or 6 or 9 or 12.
28	4. Sizes more than 12 pairs are split into multiples of 12 and assigned box number
29	5. Sizes less than 12 are grouped with other sizes to make up 12 pairs if possible to form a box.
30	6. 3 can join only with 9, if this option is not available then the second option is to join with 6 & 3 . Simillarly 6 can join only with 6 or 3 & 3. 9 can join with 3
31	7. A box can have minimum one size and maximum of 4 sizes
32
33	A typical grouping of size / pairs into Box numbers for the above 3 lot numbers is as follows:
34
35	Lot No.	12345
36	Box Number	1	2	3	4	5	6	7	8	9	10	11	12
37	Size	2.5	3.5	4.5	5.5	6.5	6.5	7	7	7	7.5	7.5	7.5
38	Pairs	(1-3)	(1-6)	(1-12)	(1-12)	(1-12)	(13-24)	(1-12)	(13-24)	(25-36)	(1-12)	(13-24)	(25-36)
39
40	Size	4	5.5
41	Pairs	(1-9)	(13-15)
42
43	Size		10.5
44	Pairs		(1-3)													*
45
46	Size
47	Pairs
48
49	Lot No.	67890
50	Box Number	1	2	3	4	5	6	7	8	9	10	11	12
51	Size	3	3	3.5	5	5.5	8.5
52	Pairs	(1-12)	(13-15)	(1-3)	(1-12)	(1-12)	(1-12)
53
54	Size		4.5	4
55	Pairs		(1-9)	(1-3)
56
57	Size			5.5
58	Pairs			(13-15)
59
60	Size			7
61	Pairs			(1-3)
62
63	Lot No.	78901
64	Box Number	1	2	3	4	5	6	7	8	9	10	11	12
65	Size	3.5	5.5	7.5
66	Pairs	(1-3)	(1-3)	(1-3)
67
68	Size	4	6	8
69	Pairs	(1-3)	(1-3)	(1-3)
70
71	Size	4.5	6.5	8.5
72	Pairs	(1-3)	(1-3)	(1-6)
73
74	Size	5	7
75	Pairs	(1-3)	(1-3)
76
Problem Description

Cell Formulas
Range	Formula
V18:V20	V18	=SUM(B18:U18)

 

[KRice]

In your example, for lot 78901, why are the partial boxes of sizes 5 (1-3) and 7 (1-3) not combined with other groupings to make 12 in those other groups? For example, the grouping of sizes 3.5 (1-3), 5.5 (1-3), and 7.5 (1-3) has a total of 9. Couldn't size 5 (1-3) be added to that group to make 12?

Similarly, couldn't size 7 (1-3) be added to the other grouping of sizes 4 (1-3), 6 (1-3), and 8 (1-3) to make 12?

Also, for lot 12345, why are sizes 2.5 (1-3), 3.5 (1-6) not combined with the partial size 10.5 (1-3) to make 12?

I don't think any of these further consolidations would violate any of the rules described. Am I misunderstanding something?

 

[Bala216]

In your example, for lot 78901, why are the partial boxes of sizes 5 (1-3) and 7 (1-3) not combined with other groupings to make 12 in those other groups? For example, the grouping of sizes 3.5 (1-3), 5.5 (1-3), and 7.5 (1-3) has a total of 9. Couldn't size 5 (1-3) be added to that group to make 12?

Similarly, couldn't size 7 (1-3) be added to the other grouping of sizes 4 (1-3), 6 (1-3), and 8 (1-3) to make 12?

Also, for lot 12345, why are sizes 2.5 (1-3), 3.5 (1-6) not combined with the partial size 10.5 (1-3) to make 12?

I don't think any of these further consolidations would violate any of the rules described. Am I misunderstanding something?

Hi...

Thanks for your reply and query...

Actually the order of filling the box is disciplined from left to right of the sizes, means starting from smaller sizes to bigger sizes. So when combining the partial boxes, first we take the pairs of partial box on the left most side and look for the pairs of other size to make it full box with 12 pairs. While doing so, point-6 in the objective which i have mentioned is considered..

Hope I clarified your doubt... If any information is still missing to solve the problem please let me know... Thank you...

 

[KRice]

Thanks for clarifying part of the issue...you've added a new rule that says to begin with smaller sizes and move to the right (larger sizes) while constructing full sets of 12. But I don't think you've really answered my questions. Have a look at lot 12345. If you start at the left (smaller sizes) and begin building "full" sets of 12 using the remainders leftover after the initial complete sets of 12 are defined, and if you have another rule that states that remainders are not to be further subdivided, then wouldn't size 2.5 (1-3) be combined with size 3.5 (1-6) and with size 5.5 (13-15) for a total of 12? And then going back through the unused partials, we would combine size 4 (1-9) with size 10.5 (1-3) for a 2nd set of 12.

Yet in your example, size 2.5 and size 3.5 are not combined with anything, so you haven't started at the left. And neither of those sizes is used elsewhere in the formation of a set of 12...they are left as separate, stand-alone partial boxes. Similarly, size 10.5 (1-3) is left as a stand-alone, without combining it with anything. Could you clarify why this is the case? Rule 6 says to combine partial sets of 3, 6, and 9 with each other to make a complete set of 12, but that hasn't been done in this example, hence my confusion.

 

[Bala216]

Thanks for clarifying part of the issue...you've added a new rule that says to begin with smaller sizes and move to the right (larger sizes) while constructing full sets of 12. But I don't think you've really answered my questions. Have a look at lot 12345. If you start at the left (smaller sizes) and begin building "full" sets of 12 using the remainders leftover after the initial complete sets of 12 are defined, and if you have another rule that states that remainders are not to be further subdivided, then wouldn't size 2.5 (1-3) be combined with size 3.5 (1-6) and with size 5.5 (13-15) for a total of 12? And then going back through the unused partials, we would combine size 4 (1-9) with size 10.5 (1-3) for a 2nd set of 12.

Yet in your example, size 2.5 and size 3.5 are not combined with anything, so you haven't started at the left. And neither of those sizes is used elsewhere in the formation of a set of 12...they are left as separate, stand-alone partial boxes. Similarly, size 10.5 (1-3) is left as a stand-alone, without combining it with anything. Could you clarify why this is the case? Rule 6 says to combine partial sets of 3, 6, and 9 with each other to make a complete set of 12, but that hasn't been done in this example, hence my confusion.

Hi..

Here is the detail which i try to explain and hope this convinces you about the logic and requirement I mean..

let us take the lot # 12345 :

2.5(1-3) / 3.5(1-6) / 4 (1-9) / 4.5(1-12) / 5.5(1-12) ; 5.5(13-15) / 6.5(1-12) ; 6.5 (13-24) / 7 (1-12) ; 7 (13-24) ; 7 (25-36) / 7.5 (1-12) ; 7.5 (13-24) ; 7.5 (25-36) / 10.5 (1-3)

First we start from left...
Box-1 :
With size 2.5 ( 1-3 ) Pairs... So this needs now 9 more pairs to complete the box.

As per Rule-6, Priority-1 : 3 pairs can join with 9 pairs to complete a box ; if not then,
Priority -2 : 3 pairs can join with 6 and 3 pairs to complete a box ; if not then,
Priority -3 : 3 pairs can join with 3, 3 & 3 pairs to complete a box.

So going by priority-1, size 2.5 (1-3) surpasses 3.5(1-6) and seeks 4 (1-9) to complete the box. (The search continues for entire length to meet the priority in order)

Now Box-1 is complete with 2.5(1-3) Pairs and 4(1-9) Pairs.

Box-2:

Now moving towards right, the next size we take into account is 3.5(1-6) Pairs... So this needs now 6 pairs to complete the box.

As per Rule-6, Priority -1 : 6 pairs can join with 6 pairs to complete a box; if not then,
Priority -2 : 6 pairs can join with 3 & 3 pairs to complete a box.

In our case, the priority-1 scenario is not available means no separate 6 pairs is available and therefore going by Priority-2,
size 3.5(1-6) seeks size 5.5(13-15)- 3 pairs and size 10.5(1-3)- 3pairs to complete the box .

Now Box- 2 is completed with 3.5(1-6) : 5.5 (13-15) : 10.5 (1-3) Pairs.

From Box-3 to Box-12 the course moves from left to right in normal way to complete the sequence...

Hope I explained it clear now and looking forward for the means to achieve it.... Thank you...

 

[KRice]

Yes, thank you...that does help. I was misinterpreting your summaries, thinking that the partials to be combined were displayed on the same row, but now I see that they are displayed vertically. Part of this is readily done with formulas, but combining the partials to form boxes of 12 using the priorities you've described and the method that may involve making multiple passes through the list of partials may lend itself better to a VBA solution. I'll give this some more thought.

 

[Bala216]

Hi..

I am glad that you got the point now..solution in VBA is good. But will it not be possible without VBA?

 

[KRice]

A formula based solution is possible, but I think it will require a helper section. I have another question about the procedure, and I would like to use your solutions for lot numbers 67890 and 78901 as the examples:

For lot 67890, when you formed box 2, you started at the left and found a partial of 3 (size 3) and then you searched toward the right for a partial of 9 because that is the highest priority match, and you found a partial of 9 (size 4.5). In doing so, you passed by lower priority matches of 3 (sizes 3.5 and 4).

For lot 78901, when you formed box 1, you started at the left and found a partial of 3 (size 3.5), and then presumably you searched toward the right for a partial of 9 because that is the highest priority match, but there was none. So then should you not have searched for a partial of 6 since that is the next highest priority match? There is a partial of 6 (size 8.5), but it was passed over and not used in box 1. Instead the lowest priority match of 3 (size 4) was used next.

There is an inconsistency here in the procedure that I cannot reconcile, and it creates a problem when attempting to develop a solution. Could you clarify why these two are handled differently?

In the shortened example below, I have 8 partials shown in the first row, and an attempt is made to make sets of 12 from those partials. For Box 1, is the selection correct?...we start at left with the first 3 and then look toward the right for the highest priorities (9, 6, or 3, in that order). We find a 6, and then we need one more partial of 3. Which 3 is chosen?...is it A, B, C, D, or E? I'm assuming it is either A or C, depending on whether the search continues to the right from the last number, or does it start again from the left side?

For Box 2, we will begin again from the left and a partial of 3 (which 3 do we begin with?...either C or D?), and then we search toward the right for the highest priority match (9, 6, or 3, in that order), and we'll find a 6...then what? I'm trying to understand the search rules.

3​	3​	3​	3​	6​	6​	3​	3​	initial partials associated with some sizes​
3​	C​	D​	E​	6​	​	A​	B​	box 1 consisting of 3 + 6 + (which 3?...A, B, C, D, or E)​
​	​	​	​	​	6​	​	​	box 2 consisting of which 3? + 6 + which 3?

 

[KRice]

Here is a revised version based on one interpretation of the rules. This always begins searching from the left anytime a new partial is needed, and the search order follows the priorities where larger partials are preferred to complete the set of 12 based on the amount in the bin being filled. At most there can be 10 boxes constructed of partials, and in most cases, that many will not be necessary. This lays out a table for filling 6 boxes. Each of the blocks surrounded by a border (4 rows in height) represent the computation block for filling the constructed box with partials, if possible. The number of constructed partial boxes can be extended by copying a complete computation block (both the left and right sides enclosed in borders) and simply pasting them one after the other. Size limitations in XL2BB prevented the entire 10-block version from being posted. I am not entirely sure that all of the error trapping is in place, but this seems to give reasonable results in the limited testing I performed. The solution for each constructed box of partials is found in the yellow-orange shaded cells at right. The initial full boxes for each size are shown in another set of yellow-orange cells on row 17.

MrExcel_20200613.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Box Number Generator
2
3	Sizes / Pairs	2.5	3	3.5	4	4.5	5	5.5	6	6.5	7	7.5	8	8.5	9	9.5	10	10.5	11	11.5	12
4	Lot Number:																					Total Pairs
5	12345	3		6	9	12		15		24	36	36						3				144
6	67890		15	3	3	9	12	15			3			12								72
7	78901			3	3	3	3	3	3	3	3	3	3	6								36
8	test1	6	9	15	9	18	3	24	3	27	6	33	3		12	9	39	6	9	42	21	294
9
10	Lot No.
11	67890
12	Sizes	2.5	3	3.5	4	4.5	5	5.5	6	6.5	7	7.5	8	8.5	9	9.5	10	10.5	11	11.5	12	Total
13	Pairs (P)	0	15	3	3	9	12	15	0	0	3	0	0	12	0	0	0	0	0	0	0	72		72
14	N boxes initially	0	1	0	0	0	1	1	0	0	0	0	0	1	0	0	0	0	0	0	0	48	<--total full & total partials-->				24
15	Column Index	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
16	P mod 12	0	3	3	3	9	0	3	0	0	3	0	0	0	0	0	0	0	0	0	0
17		0	0	3	3	9	0	3	0	0	3	0	0	0	0	0	0	0	0	0	0		sub1	sub2	sub3	sub4	total
18		0	0	3	3	0	0	3	0	0	3	0	0	0	0	0	0	0	0	0	0	Col Idx	2	5
19	Complete	0	0	3	3	0	0	3	0	0	3	0	0	0	0	0	0	0	0	0	0	Size	3	4.5
20	Constructed Box 1	0	0	3	3	0	0	3	0	0	3	0	0	0	0	0	0	0	0	0	0	Qty	3	9			12
21		0	0	0	3	0	0	3	0	0	3	0	0	0	0	0	0	0	0	0	0		sub1	sub2	sub3	sub4	total
22		0	0	0	0	0	0	3	0	0	3	0	0	0	0	0	0	0	0	0	0	Col Idx	3	4	7	10
23	Complete	0	0	0	0	0	0	0	0	0	3	0	0	0	0	0	0	0	0	0	0	Size	3.5	4	5.5	7
24	Constructed Box 2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Qty	3	3	3	3	12
25		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		sub1	sub2	sub3	sub4	total
26		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Col Idx
27	Empty	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Size
28	Constructed Box 3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Qty					0
29		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		sub1	sub2	sub3	sub4	total
30		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Col Idx
31	Empty	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Size
32	Constructed Box 4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Qty					0
33		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		sub1	sub2	sub3	sub4	total
34		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Col Idx
35	Empty	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Size
36	Constructed Box 5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Qty					0
37		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		sub1	sub2	sub3	sub4	total
38		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Col Idx
39	Empty	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Size
40	Constructed Box 6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Qty					0
Box Generator2

Cell Formulas
Range	Formula
W13,W5:W8	W5	=SUM(C5:V5)
C13:V13	C13	=INDEX(C$5:C$8,MATCH($B$11,$B$5:$B$8,0))
C14:V14	C14	=(INDEX(C$5:C$8,MATCH($B$11,$B$5:$B$8,0))-C16)/12
W14	W14	=SUM(C14:V14)*12
Y13	Y13	=SUM(W14,AB14)
AB14	AB14	=SUMIF(AB17:AB56,">0")
C16:V16	C16	=MOD(INDEX(C$5:C$8,MATCH($B$11,$B$5:$B$8,0)),12)
C37:V37,C33:V33,C29:V29,C25:V25,C21:V21,C17:V17	C17	=C16-IF($X18=C$15,C16,0)
C38:V38,C34:V34,C30:V30,C26:V26,C22:V22,C18:V18	C18	=C17-IF($Y18=C$15,C17,0)
C39:V39,C35:V35,C31:V31,C27:V27,C23:V23,C19:V19	C19	=C18-IF($Z18=C$15,C18,0)
C40:V40,C36:V36,C32:V32,C28:V28,C24:V24,C20:V20	C20	=C19-IF($AA18=C$15,C19,0)
X18,X38,X34,X30,X26,X22	X18	=IFERROR(AGGREGATE(15,6,(COLUMN($C16:$V16)-COLUMN($B16))/($C16:$V16>0),1),"")
Y18,Y38,Y34,Y30,Y26,Y22	Y18	=IF(SUM($X20:X20)=12,"", IF(SUM($X20:X20)=3,IFERROR(MATCH(9,$C17:$V17,0), IFERROR(MATCH(6,$C17:$V17,0), IFERROR(MATCH(3,$C17:$V17,0),"none" ))), IF(SUM($X20:X20)=6, IFERROR(MATCH(6,$C17:$V17,0), IFERROR(MATCH(3,$C17:$V17,0),"none" )), IF(SUM($X20:X20)=9, IFERROR(MATCH(3,$C17:$V17,0),"none" ),"") )))
Z18,Z38,Z34,Z30,Z26,Z22	Z18	=IF(SUM($X20:Y20)=12,"", IF(SUM($X20:Y20)=3,IFERROR(MATCH(9,$C18:$V18,0), IFERROR(MATCH(6,$C18:$V18,0), IFERROR(MATCH(3,$C18:$V18,0),"none" ))), IF(SUM($X20:Y20)=6, IFERROR(MATCH(6,$C18:$V18,0), IFERROR(MATCH(3,$C18:$V18,0),"none" )), IF(SUM($X20:Y20)=9, IFERROR(MATCH(3,$C18:$V18,0),"none" ),"") )))
AA18,AA38,AA34,AA30,AA26,AA22	AA18	=IF(SUM($X20:Z20)=12,"", IF(SUM($X20:Z20)=3,IFERROR(MATCH(9,$C19:$V19,0), IFERROR(MATCH(6,$C19:$V19,0), IFERROR(MATCH(3,$C19:$V19,0),"none" ))), IF(SUM($X20:Z20)=6, IFERROR(MATCH(6,$C19:$V19,0), IFERROR(MATCH(3,$C19:$V19,0),"none" )), IF(SUM($X20:Z20)=9, IFERROR(MATCH(3,$C19:$V19,0),"none" ),"") )))
X19,X39,X35,X31,X27,X23	X19	=IFERROR(INDEX($C$12:$V$12,X18),"")
Y39:AA39,Y35:AA35,Y31:AA31,Y27:AA27,Y23:AA23,Y19:AA19	Y19	=IFERROR(IF(Y18="","",INDEX($C$12:$V$12,Y18)),"")
B19,B39,B35,B31,B27,B23	B19	=IF(AB20=0,"Empty",IF(AB20=12,"Complete",IF(AB20<12,"Partial")))
X20,X40,X36,X32,X28,X24	X20	=IFERROR(INDEX($C16:$V16,MATCH(X19,$C$12:$V$12,0)),"")
Y20,Y40,Y36,Y32,Y28,Y24	Y20	=IFERROR(IF(Y18="","",INDEX($C17:$V17,MATCH(Y19,$C$12:$V$12,0))),"")
Z20,Z40,Z36,Z32,Z28,Z24	Z20	=IFERROR(IF(Z18="","",INDEX($C18:$V18,MATCH(Z19,$C$12:$V$12,0))),"")
AA20,AA40,AA36,AA32,AA28,AA24	AA20	=IFERROR(IF(AA18="","",INDEX($C19:$V19,MATCH(AA19,$C$12:$V$12,0))),"")
AB20,AB40,AB36,AB32,AB28,AB24	AB20	=SUM(X20:AA20)

Cells with Data Validation
Cell	Allow	Criteria
B11	List	=$B$5:$B$8

 

[Bala216]

Hi there...

It's my mistake. You are right... Lot# 78901 should have been grouped like below, going by the rules....Thanks for understanding and also highlighting the problem...

Regarding your query for grouping of 8 partials following will be the result going by the rules...
means, for Box-1, 3 from C should be taken into account.. and for Box-2... 3 from D,E and 6 as you have mentioned should be considered...

Thanks for your valuable time and work out...
Let me try out your formulas and solution and come back if there is any doubt...

Once again thank you very much for your patience, time and effort to solve my problem...