RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 810
- Office Version
- 365
- Platform
- Windows
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.
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.
