Jul 02 2018
03:14 AM
- last edited on
Jul 31 2018
08:36 AM
by
TechCommunityAP
Jul 02 2018
03:14 AM
- last edited on
Jul 31 2018
08:36 AM
by
TechCommunityAP
Hi all
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.
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.
I have attached the excel here for further assistance!
Appreciate of any help!
Thank you!
Kelvin
Jul 02 2018 03:52 AM - edited Jul 02 2018 03:52 AM
*
@kelvin teo wrote:my team did a formula with Min, Max and Countif, to allocate the data into groups of 8
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
Jul 02 2018 05:31 AM - edited Jul 02 2018 06:24 AM
@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
Jul 02 2018 06:34 AM
Kelvin,
it's still not clear to me. Could you provide the desired results?
Jul 02 2018 06:50 AM
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
Jul 02 2018 07:16 AM
That is still only a description but no specific results.
Jul 02 2018 07:34 AM
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
Jul 02 2018 08:31 AM
Well, it seems to me you and your team are not sure what the result should look like.
I certainly do not know. Maybe someone else has a genius intuition.
Jul 03 2018 11:41 PM
Hi @Detlef_Lewin and everyone
Apologies as I didn't make myself clear. Maybe let me try again by explaining what we want to achieve:
We run a programme which brings together applicants from different backgrounds to improve their appreciation of diversity. When they register for the programme online, they provide personal data, from which we use three categories to sort them into groups of 12 applicants each. Each of these categories have fixed variables, as listed below.
Name of applicant | Category 1 (There are 4 possible variables – C, M, I and O) | Category 2 (There are 2 possible variables – 1 and 2) | Category 3 (There are 2 possible variables –A and B) |
A001 | C | 2 | A |
A002 | C | 1 | A |
A003 | C | 2 | A |
A004 | M | 1 | A |
A005 | C | 2 | A |
A006 | C | 2 | A |
A007 | M | 2 | A |
A008 | C | 2 | A |
A009 | O | 1 | A |
A010 | C | 2 | A |
A011 | C | 1 | A |
A012 | C | 1 | A |
A013 | I | 2 | A |
A014 | M | 1 | A |
A015 | C | 1 | A |
A016 | C | 2 | A |
A017 | M | 2 | A |
A018 | C | 2 | A |
A019 | C | 1 | A |
A020 | C | 2 | A |
A021 | C | 2 | A |
A022 | I | 1 | A |
A023 | M | 1 | A |
A024 | C | 1 | A |
A025 | C | 1 | A |
A026 | C | 1 | B |
A027 | M | 1 | B |
A028 | C | 2 | B |
A029 | C | 2 | B |
A030 | C | 2 | B |
Using categories 1, 2 and 3, we want to assign group numbers to each applicant, ensuring that
· all applicants labelled C in category 1 are evenly spread out in all groups,
· all applicants labelled B are evenly spread out in all groups, and so forth.
Our earlier attempt was to concatenate the 3 categories and use a COUNTIF on the concatenated code to assign the group numbers. That didn’t work :(
Appreciate suggestions!
Thank you!
Kelvin
Jul 05 2018 12:26 AM
You still have not shown the desired results for your sample data.
For example: how would you evenly distribute the applicants with label O in category 1 into 8 groups? There is only 1 applicant.
Nov 07 2023 08:48 AM - edited Nov 07 2023 08:49 AM
@kelvin teo did you find a solution for this ? I am trying to do the same thing. thanks!