# Randomly Assigning Sales Reps

Occasional Contributor

# Randomly Assigning Sales Reps

Hello! - See attached for a sample sheet. I am randomly assigning sales representatives to contacts in a spreadsheet. The trick is that there are multiple contacts under the same account. How can I evenly assign my representatives to accounts but ensure that two representatives do not get the same account?

4 Replies

# Re: Randomly Assigning Sales Reps

The best way would be to create Account IDs for an IF function to reference when applying the RANDBETWEEN syntax (combined with INDEX)

# Re: Randomly Assigning Sales Reps

If I understand what you're asking, I would create a unique list of account names and randomly assign sales reps evenly to the individual accounts.

I think you could use excels randbetween function to assign sales reps, but it would require a circular reference. I attached a workbook as an example with some instructions.

# Re: Randomly Assigning Sales Reps

@adversi Can you give an example of the formula?

# Re: Randomly Assigning Sales Reps

if we assume Casper Heating is Account ID 1, then the formula would be broken in two sections

``=INDEX(\$F\$4:\$F\$6,RANDBETWEEN(1,ROWS(\$F\$4:\$F\$6)),1)``

Every cell after A2 would have the condition to review previous results:

``=IF(D3=D2,A2,INDEX(\$F\$4:\$F\$6,RANDBETWEEN(1,ROWS(\$F\$4:\$F\$6)),1))``

An important note is that two new columns should be made to finalize the dataset.

1. TRIM the Account Names to make the data uniformed and automatically remove additional white spaces

2. Account IDs that you link using VLOOKUP or INDEX/MATCH to get the reference for the IF syntax