Question on unique and sampling

Dear community member

I have a specific use case which I would like to seek some help.

I was given a list of names and groups as below.

Name	Group
John	A
Mary	A
Peter	A
John	B
Charles	B
Peter	B
John	C

I need create a unique list of names, but at the same time I need to ensure there is at least 1 name in each group.

Mary and Charles are unique in the list; so they are first selected in the output.

John appears in Group A, B, and C. But John is the only person in Group C. Therefore John / Group C must be selected to ensure there are at least 1 participant in each group.

The remainder is Peter, who belongs to both Group A and B. I just randomly select a group for Peter.

The final output will be

Name	Group
Mary	A
Charles	B
John	C
Peter	Randomly choose either A or B

Could someone please shed some lights on how I can achieve this? I start off with using Unique and sample and rand() but no luck so far.

Thanks in advance.

Sample Data and Expected Output.xlsx

Workflow

Preparation

Accepted answers

danilang

Hi @yikl

While The solution from @SamDesk brings you close, it doesn't randomize the selection for groups where there are mulitple possibilities

This solution finds the unique groups and names combinations first and assigns the rest randomly. Note that I added Bill to groups A and B

Dan

Sample and random.yxmd

All comments

SamDesk

Hello @yikl,

To achieve what you are looking for it is important to know that the Unique tool works from top to bottom through your data, meaning that the first unique value it finds is the one that it keeps. Knowing this we can sort your data based on the number of members in a group so that the groups with the least number of members appear at the top, thus ensuring that if there are any duplicate names across groups, the one with the least members will get them.