Distribute (join) rows evenly

[arturo_garcia]

Hello,

I am trying to distribute audits (rows) evenly among a list of auditors. For example, if I have 40 audits to be done and 7 auditors, how can I distribute (join) this evenly among the 7 auditors? Keep in mind number of audits and auditors can vary.

Audit No
1
2
....
40

Auditor ID
A
B
C
D
E
F
G

Expected result:

Audit No	Auditor
1	A
2	A
3	A
4	A
5	A
6	A
7	B
8	B
9	B
10	B
11	B
12	B
13	C
…
34	F
35	G
36	G
37	G
39	G
40	G

So 5 auditors would get 6 audits and 2 would get 5. I tried the following formula but it is not working with all cases:

ceil([AuditNo]/(ceil([TotalAudits]/[TotalAuditors])))

[Aaron_Harter · Accepted solution]

Hi @arturo_garcia, 

I've designed some logic to first determine how many Audits should be mapped to each Auditor ID, and then map those values in the desired order: