Looking for a way to allocate values according to a percentage split, plus a couple other variables in VBA

[RockandGrohl]

Hello everybody! (HI DR. NICK!)

I'm after a semi-intelligent automated solution to a manual task one of our directors does, he goes through a list of 1,000 clients each week and assigns a brand to them based off a bunch of variables and calculations he performs -imperfectly- on the spot.

On one sheet, we have a list of 1,000 different clients and a "Y/N" column of whether or not they are available.

I have a userform with a slider on it that determines a percentage split, as we have two different brands, but we like to prefer one brand more than another.

So there would be a variable which could be something like 0.1 or 0.25 or 0.46 (10%/25%/46%)

This then affects the proportion of brands assigned to each of the 1,000 clients.


Of these 1,000 clients, let's say 400 have "N" next to them, so are ignored.

Making a count of the "Y" clients gives 600 clients to be assigned a brand (Either JG or OM)

If we think our slider is on 33%, this means I want approximately 200 clients for OM and 400 for JG.

What I need is to intelligently assign these clients. On another workbook is a "league table" where these papers have a Return of Investment figure against each of the two brands.

As an example, if we have a paper such as the "Swindon Standard" it might have 6 ROI for JG, but only 2.3 for OM, so we would want to assign JG to this client as they are the stronger brand for that paper.


So I think what I need is for it to look at the 200 best papers for "OM", assign them, then assign the rest to JG and prioritize the best for JG.


Do you guys have any ideas on where I could start? Thanks.

 

[offthelip]

The way I would do this is to do a countif of the Y/N to find how many clients you need to deal with, then take the percentage of these that you want to assign to OM ,assume this is a number K
Then use the RANK() function on the ROI for OM to find the rank of every client, then all the ones that you want to assign to OM will be those with a rank greater than K