Nov 12 2020 11:48 AM
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:
Nov 12 2020 12:06 PM
Nov 12 2020 01:07 PM
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.
Nov 13 2020 04:56 AM
Nov 14 2020 10:50 AM - edited Nov 14 2020 10:56 AM
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