Distribute data without repition

Hi All,

I am in need of a little help....

I organise monthly audits of work completed by the team. Essentially we all audit each others work; but we cannot audit our own work.

I would like a formula that will evenly distribute the jobs for audit without passing a job back to someone that owns the job originally. e.g. Gareth cannot audit a job Gareth owns, but can audit Zach or Claire's jobs. The Excel sheet attached should make it easy to understand.

So far I have tried: =INDEX(A2:A24,RANDBETWEEN(2,24)) but this duplicates data and I cannot get it to evenly spread the jobs amongst the team.

..... Can anyone help?

Hi garethrobertsleeds ,

In the attached sheet run the macro "altcolor" and see if the results meet your expectations.
It would color the cell against the job as yellow for which the audit is to be done.
NOTE: remove the color as no fill, everytime you run the macor again.







<If this helped, then please say thanks by adding some reputation by clicking on the star * button below this post>

This is a great way of doing it Bhuvi

Is there anyway to try and distribute a little more evenly, I'm not sure if this is possible?

I appreciate Zach has completed more jobs so can audit less in comparrison but Claire appears to be able to audit roles that I have completed that Zach has availability to audit.