Random assigning

[AlanMaga]

Dear all,

I need to randomly group people into groups of 2 as indicated in the picture and randomly assign an analyst to prepare documents for the particular groups with the help of excel functions. This needs to be repeatable on a weekly basis. 

What I have done is that i assigned random numbers to Members, these are then split randomly into 5 groups.

=CEILING.MATH(RANK.EQ([@RAND],[RAND])/$H$2) - to randomly assign a group number 

=FILTER(Table1[[Member]:[Member]],Table1[[ROUND]:[ROUND]]=G4,"") - to sort them into the groups 

Then I have prepared the 5 analysts that prepare the documentation for the groups. Now, the issue that I can't solve is that the formula  (=XLOOKUP(INDEX(UNIQUE(RANDARRAY(M10^2,1,M11,M12,TRUE)),SEQUENCE(M10)),I10:I14,J10:J14))
doesn't recognize if the analyst is present in the group, for which he/she is supposed to do the documentation, which is of course not correct. 

Could you help me out with this ? Any suggestions are welcome, even if I should start from scratch with an easier/more convenient/ more logical way. 

Thanks.

[#2]

@AlanMaga I'm a bit confused with the logic here. Are the QC Analysts always the same 5 people (shown in range J10:J14)? If so, then I'm thinking they shouldn't be listed in the same table as the other members, because the formula you're using in the ROUND column can end up placing two analysts in one group (as is the case with group 5 in your screenshot). If the goal is to randomly place members into 5 groups, then randomly assign an analyst to each group, the lists should be separated. Also, your XLOOKUP formula in cell C21 can probably be simplified with:

=SORTBY(J10:J14, RANDARRAY(5))

I hope that helps...