Assign to numbered Group with max Group Size

[hmltnangel]

Hi All,

Hopefully you can help with this formula

In Table1, Column "Suggested HPO Group" I have the following formula

=IF([@[HPO Champion]]<>"","",IF(ROUNDUP(RANK([@Rand],[Rand])/$G$1,0)=[Managers HPO Group],ROUNDUP(RANK([@Rand],[Rand])/$G$1,0)+1,ROUNDUP(RANK([@Rand],[Rand])/$G$1,0)))

(NOTE - G1 calculates teh Max Group Size)

In Table1 Column "Final HPO Group" I have the following:

=IF(AND([@[Original HPO Group]]>0,[@[Original HPO Group]]<>[@[Managers HPO Group]]),[@[Original HPO Group]],IF([@[HPO Champion]]<>"","",IF(ROUNDUP(RANK([@Rand],[Rand])/$G$1,0)=[Managers HPO Group],ROUNDUP(RANK([@Rand],[Rand])/$G$1,0)+1,ROUNDUP(RANK([@Rand],[Rand])/$G$1,0))))

Now teh Final HPO Group works well for the first part - retaining the original group if there is a group there, or the managers group is not the same. What I want is for it to then take everyone this formula doesnt cover, and plit those people up between all the people in the HPO Champion Group,s but ensure that the max in each group doesnt exceed the Max Group Size.

ANy suggestions? Tearing my hair out with this one :O