Forum Discussion
Allocation and grouping of data based on criteria
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
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.
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