Excel Problem Help Required

[purceld2]

I have a spreadsheet which contains information of all the advertisement panels/sites that we have with a 10 mile proximity of our customers stores/dealerships. The list contains over 2000 sites but covering 74 of the customers 79 stores. My problem is that the customer only has enough budgets to purchase 280 panels.

Issue is that he wants as many of his store covered as possible. I have attached a pivoit table which provide detail of the amount of panels covering each of the customers sites/dealerships.


Other than manually going through and selecting panels/sites, is they a way of sweeping through the list picking 1 from each dealership until the desired total has been reached 280.

This is something I do quite regularly and wondered if a bit of VB code could do the trick.

Any help would be most appreciated.

Excel 2010
	A	B
3	Row Labels	Count of Customer Venue Name
4	Bell Truck & Van (Billingham)	5
5	Bell Truck & Van (Longbenton)	22
6	Bell Truck & Van (Stockton)	5
7	Caledonian Truck & Van	6
8	Ciceley Commercials Blackburn	16
9	Ciceley Commercials Carlisle	7
10	Ciceley Truck & Van Centre	28
11	Enza Motors Stoke	17
12	Enza Motors Trafford Park	53
13	Enza Motors Warrington	28
14	Euro Commercials Cardiff	10
15	Euro Commercials Ltd	8
16	Euro Commercials Swansea	7
17	Hughes of Aylesbury	11
18	Intercounty Truck and Van (Boston)	4
19	Intercounty Truck and Van (Lincoln)	2
20	Intercounty Truck and Van (Milton Keynes)	19
21	Intercounty Truck and Van (Peterborough)	13
22	Intercounty Truck and Van Limited	12
23	John R Weir Perth	3
24	Mercedes-Benz Brentford	148
25	Mercedes-Benz City West Commercials (Avonmouth)	17
26	Mercedes-Benz City West Commercials (Bristol)	15
27	Mercedes-Benz City West Commercials (St. Austell)	2
28	Mercedes-Benz Colindale	131
29	Mercedes-Benz Commercial Vehicles Aberdeen	11
30	Mercedes-Benz Croydon	122
31	Mercedes-Benz of Weston-super-Mare	2
32	Mercedes-Benz Retail Used Cars East London	100
33	Mertrux Limited (Derby)	16
34	Mertrux Limited (Leicester)	21
35	Mertrux Limited (Nottingham)	21
36	Midlands Truck & Van (Birmingham)	106
37	Midlands Truck & Van (Coventry)	20
38	Midlands Truck & Van (Wolverhampton)	36
39	Northside Truck & Van Ltd (Bradford)	20
40	Northside Truck & Van Ltd (Doncaster)	11
41	Northside Truck & Van Ltd (Hull)	5
42	Northside Truck & Van Ltd (Immingham)	4
43	Northside Truck & Van Ltd (Leeds)	30
44	Northside Truck & Van Ltd (Sheffield)	17
45	Northside Truck & Van Ltd (York)	4
46	Orwell Truck and Van - Colchester	11
47	Orwell Truck and Van - Ipswich	8
48	Orwell Truck and Van - Newmarket	7
49	Orwell Truck and Van - Norwich	9
50	Pentagon Andover	9
51	Pentagon Fareham	24
52	Pentagon Poole	18
53	Road Range Commercials (Deeside)	22
54	Road Range Commercials (Liverpool)	32
55	Road Range Commercials (North Wales)	3
56	Road Range Van Centre Liverpool	30
57	Rossetts Commercials Aldershot	37
58	Rossetts Commercials Crawley	30
59	Rossetts Commercials Eastbourne	9
60	Rossetts Commercials Worthing	17
61	Rygor Commercials Heathrow	109
62	Rygor Commercials Kidderminster	14
63	Rygor Commercials Oxford	15
64	Rygor Commercials Reading	27
65	Rygor Commercials Swindon	15
66	Rygor Commercials Tewkesbury	8
67	Rygor Commercials Westbury	7
68	Rygor Newbury	9
69	S & B Commercials Hatfield	51
70	S & B Commercials Stansted	11
71	S & B Commercials West Thurrock	59
72	S G Smith (Motors) Sydenham Ltd	129
73	Sparshatts of Kent (Dartford)	74
74	Sparshatts of Kent Ltd (Sittingbourne)	9
75	Western Commercial Bellshill	35
76	Western Commercial Edinburgh	26
77	Western Commercial Glasgow	43
Sheet3

 

[pjmorris]

Hi,

try this:

1. Sort your data so that the list runs from those with the fewest boards at the top to the highest at the bottom.
2. In cell D1 insert the number of boards the customer can pay for, in this case 280.
3. In cell C2: =IF(B2 < D1/COUNTA(B2:$B$75), B2, INT(D1/COUNTA(B2:$B$75) ) )<d1 COUNTA(B2:$B$75)))<="" COUNTA(B2:$B$75),B2,INT(D1="" strong="">
</d1><d1 COUNTA(B2:$B$75)))<="" COUNTA(B2:$B$75),B2,INT(D1="" strong="">4. In cell D</d1><d1 COUNTA(B2:$B$75)))<="" COUNTA(B2:$B$75),B2,INT(D1="" strong="">2: </d1><d1 COUNTA(B2:$B$75)))<="" COUNTA(B2:$B$75),B2,INT(D1="" strong="">=D1-C2
</d1><d1 COUNTA(B2:$B$75)))<="" COUNTA(B2:$B$75),B2,INT(D1="" strong="">5.</d1><d1 COUNTA(B2:$B$75)))<="" COUNTA(B2:$B$75),B2,INT(D1="" strong=""> Select cells C2 & D2 and copy down the list.

The result will be as even a number of boards around each location as possible, but taking account of the sometimes limited numbers of boards available.

Hope this helps.

PS. Not really an Excel matter, but it might be better to consider a more targetted strategy of allocation than my solution will provide. For example if there are many boards available in an area does the customer want to have more so that his proportional presence is greater. For example give the data and my calculations in all those locations with 3 or less boards every board (100%) will present your client, but in Mercedes-Benz Brentford there will only be 4 boards or only a little over 2%.</d1><d1 COUNTA(B2:$B$75)))<="" COUNTA(B2:$B$75),B2,INT(D1="" strong=""></d1>

 

[pjmorris]

An alternative equation would be, in C2: =MAX(1,INT(B2*D1/SUM(B2:$B$75)+0.5)) and copy down.

This will give you a much more even (percentage wise) coverage. Its still far from perfect because of the impact of some areas having significantly lower numbers of boards available.

Just a thought.

 

[pjmorris]

Hi Purcel, grateful if you could let me know whether this was useful?

Regards

 

[purceld2]

My apologies for being so rude and not providing some feedback

It looks like I did not explain the problem correctly so I did not understand you solution. But I will tell you what I did to resolve the problem I had and you may be able to suggest a better solution.

I sorted the data by Dealer name then

proximity to our customer’s screens. I then performed a advance filter on the data using the dealer names as the criteria with unique option ticked.

The result was that I got the nearest screen to each dealership

I cut this data to a new Spreadsheet and repeated the process getting then next nearest for each dealership.

I repeated the process until I had hit my target of 280 screens I did for sweeps.

Regarding the "targeted strategy of allocation" this was not necessary as each dealership contributed to the cost equally.

Thank you for your time looking into my issue. Sorry again I did not provide any feedback.

Regards

Desmond

 

[pjmorris]

thanks for letting me know. As a point for the future it is worth remembering that Forum users probably don't understand the jargon that we all use in our day to day business. That is probably the route of the original confusion. But glad that you've now resolved the problem.

Regards