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.
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 |