Forum Discussion
Allocation and grouping of data based on criteria
Detlef_Lewin Thank you the fast reply!
able to help me understand the use of quotient for this case?
=QUOTIENT(ROWS(D$2:D2)-1,$M$2)+1
and what would be the use of cell "M" in the formula?
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..
Just realized i missed out mentioning about the criteria. In each group, we hope to achieve 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).
Thank you!!
Cheers
Kelvin
Kelvin,
it's still not clear to me. Could you provide the desired results?
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 number
2. 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.
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.
Hence, 1 group should have at least 1 permutation of the CODE.
Hope the above clarifies.
Thank you!
Cheers
Kelvin
That is still only a description but no specific results.
oh, what i meant is that we want to allocate the data into different groups. It is something that my team had came out with in the excel itself, under group column where we allocate them into 8 different groups:
Criteria 1 Criteria 2 Criteria 3 CODE GROUP # C 1 B C1B 8 M 1 C M1C 5 O 1 D O1D 2 C 1 D C1D 8 C 2 D C2D 8 O 2 C O2C 1 C 2 A C2A 8 C 1 D C1D 8 I 2 D I2D 2 M 1 C M1C 5 C 1 C C1C 8 C 1 C C1C 8 M 1 B M1B 3 C 2 B C2B 8 C 2 B C2B 8 C 2 B C2B 8 C 1 A C1A 7 but we want to set a limit of 12 records in each group, that's where we are unable to get our grouping correct.
so if i were to use your formula, i am not able to achieve what i want when i sort the data in ascending order using Criteria 3.
Not sure if I was able to make it clear. Let me know how best can I put this across.
Thank you so much for replying!
Cheers
Kelvin