Question on unique and sampling

[yikl]

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.

[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.

Hope this helps.

Sam :)

[danilang · Accepted solution]

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

[yikl]

Thanks so much Sam and Dan.  Very helpful.  Appreciate your immediate response.

Henry