Allocation and grouping of data based on criteria

Question

Hi all&nbsp;I have a problem here as i wanted to allocate grouping to a set of data with 3 criteria and each group has a size limit of 12.&nbsp;my team did a formula with Min, Max and Countif, to allocate the data into groups of 8 (using 100 data records to divide by 12 to get the nearest number of 8). However, we encounter an issue of the formula not able stop at the size limit of 12 in each group and weirdly there is a missing group 6 in the allocation.&nbsp;&nbsp;I have attached the excel here for further assistance!&nbsp;&nbsp;Appreciate of any help!&nbsp;Thank you!Kelvin

detlef_lewin · Answer

*kelvin teo&nbsp;wrote:my team did a formula with Min, Max and Countif, to allocate the data into groups of 8&nbsp;Your formula does nothing of that kind. It simply counts the occurrences of each CODE.Maybe you are looking for this:=QUOTIENT(ROWS(D$2:D2)-1,$M$2)+1&nbsp;

kelvin teo · Answer

Detlef_Lewin&nbsp;Thank you&nbsp;the fast reply!&nbsp;able to help me understand the use of quotient for this case?&nbsp;=QUOTIENT(ROWS(D$2:D2)-1,$M$2)+1and what would be the use of cell "M" in the formula?&nbsp;indeed we want to count the occurrence of the code but we are unable to limit it to 12 in each group. Hence we faced a road block..&nbsp;Just realized i missed out mentioning about the criteria. In each group, we&nbsp;hope to achieve&nbsp;an equal mix of Criteria 1 (same number mixing of C, M, I and O), Criteria 2 (equal mix of 1 and 2) and Criteria 3 (equal mix of A, B, C and D).&nbsp;&nbsp;Thank you!!CheersKelvin

detlef_lewin · Answer

Kelvin,&nbsp;it's still not clear to me. Could you provide the desired results?&nbsp;

kelvin teo · Answer

&nbsp;Detlef_Lewin&nbsp;the desire result would be a a good mix of the following in each group, assuming on the 100 records split into 8 groups:1. equal mix of Criteria 3: (A, B, C and D) and each of them may not be the same number2. equal mix of Criteria 2: (1 and 2) and there may not be 50 of each.3. equal mix of Criteria 1: (C, M, I, O) and each of them may not be the same number.&nbsp;Hence, we came up with the CODE columns, to have a combination of C1A, C1B, C1C so on so forth. and we hope to split the C1A, C1B etc into different groups.&nbsp;Hence, 1 group should have at least 1 permutation of the CODE.&nbsp;&nbsp;&nbsp;Hope the above clarifies.&nbsp;Thank you!CheersKelvin&nbsp; &nbsp;&nbsp;

detlef_lewin · Answer

That is still only a description but no specific results.&nbsp;

Forum Discussion

Allocation and grouping of data based on criteria

10 Replies

Resources