RANDOM function to generate teams with an exception

[iiMike]

Hello,

I have been driving myself mad trying to figure this out. I have 8 people from 8 departments that need to (randomly) team up:
Two 3 person teams and one team being only two people to perform an audit. Both 3 person teams need to audit 3 departments together and the 2 person team audits 2 departments BUT cannot end up in their own department. I may be able to get away with four 2 person teams to make it a little easier. I need to randomize teams without auditing their own departments every month, how can i get this to work in excel? I have attached an example, on the left is what i'm starting with and on the right is an example of what i need to randomly generate monthly. Please help!!!!

[tim201110]

=--AND(COUNTIF(H$3:H3,1)<=LOOKUP($F4,$S$1:$T$3),SUM($G4:G4)<=LOOKUP($F4,$S$1:$T$3),ISNA(MATCH(H$2,IF($F$4:$F$11=$F4,$E$4:$E$11),))) array formula

[iiMike]

Okay, i see.. How can i automatically assign those teams to audit departments together without auditing their own department?

[iiMike]

If i wanted to switch it to 2 person teams, how would i do so?

[Phuocam]

Try this:

=INDEX($C$4:$C$11,AGGREGATE(15,6,ROW($A$4:$A$11)/($A$4:$A$11<>E4)/(COUNTIF($G$3:G3,$C$4:$C$11)=0)
-ROW($A$3),RANDBETWEEN(1,SUMPRODUCT(($A$4:$A$11<>E4)*(COUNTIF($G$3:G3,$C$4:$C$11)=0)))))

If G11 results #NUM !, please press F9 to get new results.

[iiMike]

Perfect! You guys have been awesome thank you for the help!!! Now how do i mark this as solved?