Randomly Assigning Sales Reps

[smm10094]

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? 

@PReagan :nerd_face::raising_hands:

[#2]

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

[#3]

@smm10094 

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. 

[#4]

@adversi Can you give an example of the formula?

[#5]

.@smm10094 

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

Cell A2 would be the start with no condition:

=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