Grouping Based on Specific Criteria

[jhopton4]

I have been tasked with dynamically assigned a group of people based on specific criteria. Here is the the logic laid out below. 

I have 3 main groups and 5 sub-groups assigned to each main group. (see layout below). 

Main Group	Sub-Group
1	1
1	2
1	3
1	4
1	5
2	6
2	7
2	8
2	9
2	10
3	11
3	12
3	13
3	14
3	15

From a list of 16 options, I need to assign 5 random 'booths' to each subgroup. They can't repeat for each subgroup.

In each booth column, the 16 options can only appear once. 

See wrong and correct output examples: 

WRONG:

Main Group	Sub-Group	Booth 1	Booth 2	Booth 3	Booth 4	Booth 5
1	2	03	15	12	08	15
2	3	03	13	11	07	02

CORRECT:

Main Group	Sub-Group	Booth 1	Booth 2	Booth 3	Booth 4	Booth 5
1	2	03	15	12	08	16
2	3	04	13	11	07	02

One more Caveat is that each Main group can never be assigned 1 specific room. For example: 

Main Group 1 Can't be assigned number 5

Main Group 2 Can't be assigned number 4

Main Group 3 Can't be assigned number 13

[JohnJPS]

Quick Question: your "correct" 2nd row has Main Group = 2 and Booth 1 = 4, but your caveat states that this combination is restricted. Or does that restriction only on the Main/Sub combo?

[jhopton4]

Hi John,

Thanks for pointing that out.You are correct. For that specific scenario a 4 should never appear in that row. I messed that up in my example.

[JohnJPS · Accepted solution]

Hi @jhopton4

This isn't terribly pretty in the sense that extended it to more general cases might not work so well, but seems to do the trick for this specific problem.

Basically: first I append the input to itself; then apply your caveat exclusions along with some additional filters due to the Append Fields really blowing things up. From there I split into two streams that are basically identical: one grabs the first 5 records, the other the lasts 5. It then counts up the "booths" before Cross-Tabbing them over into columns. Finally I union the two streams together.

I don't particularly like it since (1) I glibly assume that there will be no overlap when selecting "first 5" and "last 5" and (2) there is code duplication which is generally not all that elegant; and (3) I split in two because you showed two rows...adding more rows would mean additional spltis (or perhaps an iterative macro, and then my earlier glib assumption becomes even more suspect), and (4) if you have more complex caveats, the filter could get ugly, and finally (5) appending a dataset to itself can get really ugly.  So... proceed with caution.  :-)

Hope that helps!

John