Forum Discussion

smm10094's avatar
smm10094
Copper Contributor
Nov 12, 2020

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? 

 

PReagan 🤓🙌

Formulas and Functions

4 Replies

  • JMB17's avatar
    JMB17
    Bronze Contributor
    Nov 12, 2020

    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. 

  • adversi's avatar
    adversi
    Iron Contributor
    Nov 12, 2020
    The best way would be to create Account IDs for an IF function to reference when applying the RANDBETWEEN syntax (combined with INDEX)
      • adversi's avatar
        adversi
        Iron Contributor
        Nov 14, 2020

        .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 

         

Resources