Formula for grouping based on criteria

Hi all!

We need help in coming out with a formula using Excel

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 in the attached excel sheet.


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

I'm not sure but maybe this?

Category 1	Category 2	Category 3	Name of applicant
C	2	A	A001,A003,A005,A006,A008,A010,A016,A018,A020,A021
C	1	A	A002,A011,A012,A015,A019,A024,A025
M	1	A	A004,A014,A023
M	2	A	A007,A017
O	1	A	A009
I	2	A	A013
I	1	A	A022
C	1	B	A026
M	1	B	A027
C	2	B	A028,A029,A030

could you show the result example?