Loop until the maximum number of unique staff is found

Question

Hi all,

I am building a workflow which creates a sample list from the population list. In the population list, we have 8 groups, and for each group, the sample size varies. For example, for group 1, we need to select 1 sample, for group 2, 3 samples are needed. After the whole sample list is created, we will calculate the number of distinct staffs in that sample list and find the optimal sample list that covers the maximum number of unique staffs.

I tried to build an iterative macro to find the optimal list, but failed. Is there any way to achieve this? Many thanks.

test data.xlsx

wtteleanor · Answer

Hi Qiu @Qiu

Sorry that I don't know how to describe the formula of this🤔

I tried to use batch macro to generate sample list for multiple times, like 100 times, and find the optimal one.

But it is hard to find the optimal list that covers all the staffs.

Usually the optimal list from the macro only covers around 21 to 22 staffs and is hard to get larger numbers.

Attached is my trial workflow, hope it can give you some hint.

Many thanks😁

trial.yxzp

Qiu · Answer

@wtteleanor 
I think I understand you here and it appears very mathmatical to me.

Do you happen to have formula for it. 😁

Definitely need more time..

wtteleanor · Answer

@Qiu Hi Qiu,

The sample size is fixed. The variable is the number of distinct staff from the final sample list. The goal is to generate the sample list that covers the most number of staffs. Here the total sample size is 34, the number of staff is 26, so we want to find one sample list that could cover the 26 staffs.