formula to create a repeated number sequence in a formula column

Marvin Daniels

I have a 400 claims (and growing) that need to be assigned to three claims examiners. I would like to evenly distribute the work, so I would like to create a column formula in a helper column that simply repeats 1-2-3, like this:

• row 1 - 1
• row 2 - 2
• row 3 - 3
• row 4 - 1
• row 5 - 2
• row 6 - 3

In Excel this is simple, but I'm stuck trying to do this in smartsheet.

Then it will be simple to use that value to do the assignment via an index/match:

• 1 = examiner 1
• 2 = examiner 2
• 3 = examiner 3

Open to other ideas if anyone has another approach that works.

Marvin Daniels

Please disregard my question because of course it's already been answered in this forum and I should have looked a littler hard before posting.

Used this formula:

=INDEX({assignment}, MATCH(MOD([row id]@row, MAX({seq})) + 1, {seq}, 0))

• [row ID] = auto number
• {seq} = 1, 2, 3 (xref)
• {assignment} = contact (xref)

However, this is only possible because of this @Bob Kahn solution:

https://community.smartsheet.com/discussion/81780/random-assignation

Marvin Daniels

Please disregard my question because of course it's already been answered in this forum and I should have looked a littler hard before posting.

Used this formula:

=INDEX({assignment}, MATCH(MOD([row id]@row, MAX({seq})) + 1, {seq}, 0))

• [row ID] = auto number
• {seq} = 1, 2, 3 (xref)
• {assignment} = contact (xref)

However, this is only possible because of this @Bob Kahn solution:

https://community.smartsheet.com/discussion/81780/random-assignation