|
Hello, I was wondering if the community might be able to assist me with a tool I am trying to build. Essentially, my work involves completing jobs for people. Each person is a client essentially, and there are thousands of them. We have case managers that would be responsible for portions of the population. In general, we use an alpha split to assign the work. So, I would like to build a tool that takes the raw data of our client list, and splits them between the case managers based on their last name. I would like the tool to be dynamic. For example, if the 8 case managers are listed in column A, Column B could be percentage of the case load then column C would be the alpha split to give them. Ideally I would like sheet 2 to be the raw data client list. For example: Column A: Last Name Column B: First Name Sheet 1 would be the Alpha Split tool. I think I would do total last names starting with A, B, C, D, E, ...etc. That I can do a small table with all the letters of the alphabet and a "=countif(A:A,"A*")" Where I get stuck is the alpha split part. I know I want the population split between a population of say 8 case managers, I want it to be dynamic (so maybe 10% to Case Manager 1, 15% to Case Manager 2 etc.), and I want it to tell me which letters of the alphabet to give them to get closest to that percentage. As a bonus, it would be cool to have a variance column. So If I said 10% to case manager 1, and it spits out letters A, B, what is the total population of A and B compared to the total and how does that vary from the 10% I wanted to give. Any ideas? This seems complex, but I am sure someone knows how. (责任编辑：) |