Assign a contract to a owner

Question

I'm new on VBA, pls be patient. I have a list of contracts and need to assign each unique contract to someone and in case the contract repeat in another cell it should be assigned to the same person. I have no idea how to do it. So basically, it's just for each unique value in column A insert a value in a column C from a list in the column B

Column A            Column B            Column C
MXAACIX             Alice
MXAAFO5             Carolina
MXAAFO5             Jefferson
MXAAFO5             Erik
MXAAFO5
MXAAB3T
MXAAB96
MXAAFDE
MXAAFDE
MXAAFDE
MXAAEK7
MXAAEK7

Please post the code you have written thus far and explain the errors / bugs you are running into. Also, you need to elaborate upon the logic you would like to use in distributing the contracts: what happens if there are less unique contracts than there are names? what happens if there are (for example) 10 unique contracts? Who gets more contracts than others? — Ralph, Mar 11 '16 at 18:37
If you make the assignments on one "master" sheet then you can use `VLOOKUP()` on all the other sheets to pull the assignees. — Tim Williams, Mar 11 '16 at 19:53
You can get the unique contracts by implementing this solution: http://stackoverflow.com/questions/1429899/getting-unique-values-in-excel-by-using-formulas-only But afterwards the question still remains: how do you want to split the (in this case) 6 contracts among 4 people? — Ralph, Mar 11 '16 at 22:33

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

There is a standard worksheet formula that can accomplish this. In C2 as,

=IFERROR(INDEX(C$1:C1, MATCH(A2, A$1:A1, 0)),
         INDEX(B$2:B$5, MOD(SUMPRODUCT(1/COUNTIF(A$2:A2, A$2:A2))-1, 4)+1))

To make that truly dynamic according to the number and variety of contracts and the number of owners, the formula becomes a little more complicated.

=IFERROR(INDEX(C$1:C1, MATCH(A2, A$1:A1, 0)),
         INDEX(B$2:INDEX(B:B, MATCH("zzz",B:B )),
                  MOD(SUMPRODUCT(1/COUNTIF(A$2:A2, A$2:A2&""))-1, 
                      COUNTA(B$2:INDEX(B:B, MATCH("zzz",B:B ))))+1))

^{Good documentation on individual worksheet functions can be found at Excel function (by category).}

Assign a contract to a owner

1 Answers1