Forum Discussion

kelvin teo's avatar
kelvin teo
Copper Contributor
Jul 02, 2018

Allocation and grouping of data based on criteria

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 alloc...
Formulas & Functions
Need Help
Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Jul 02, 2018

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.

 

kelvin teo's avatar
kelvin teo
Copper Contributor
Jul 04, 2018

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 

 

 

  • maymay_'s avatar
    maymay_
    Copper Contributor
    Nov 07, 2023

    kelvin teo  did you find a solution for this ? I am trying to do the same thing. thanks!

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Jul 05, 2018

    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.

     

Resources