davewatson86
New Member
- Joined
- Jul 8, 2019
- Messages
- 30
Hello all
i have this formula :{=LARGE((ROUND('Customer Data'!$Q$2:$Q$5000,5)+ROW('Customer Data'!$Q$2:$Q$5000)/10000000),ROW(K3)-ROW($K$3)+1)}
this gives me a list of the top 50 spenders of a certain product group. (3 different groups all up so i have 3 different lists)
my problem is that based on the spending in the groups the customers earn rebates, however they cannot earn rebates in more than one group.
how can i modify this to generate the top 50 list while A. qualifying the customer is unique to that list and B. if not unique keep the customer in the list where the rebate value is highest and remove the customer from the list where the rebate is lower.
my data looks like this
<tbody>
</tbody>
My lists Look Like this
<tbody>
</tbody>
obviously this is only a sample of the data as there are thousands of lines.
your help as always is greatly appreciated.
Dave
i have this formula :{=LARGE((ROUND('Customer Data'!$Q$2:$Q$5000,5)+ROW('Customer Data'!$Q$2:$Q$5000)/10000000),ROW(K3)-ROW($K$3)+1)}
this gives me a list of the top 50 spenders of a certain product group. (3 different groups all up so i have 3 different lists)
my problem is that based on the spending in the groups the customers earn rebates, however they cannot earn rebates in more than one group.
how can i modify this to generate the top 50 list while A. qualifying the customer is unique to that list and B. if not unique keep the customer in the list where the rebate value is highest and remove the customer from the list where the rebate is lower.
my data looks like this
A | B | O | P | Q | |
---|---|---|---|---|---|
196 | 102521 | Stephan Bmw | £0.00 | £0.00 | -£1.92 |
197 | 102525 | J Parker | £0.00 | £0.00 | -£0.37 |
198 | 102526 | Leeders Accident Centre Ltd | -£0.48 | £4.35 | £142.10 |
199 | 102527 | North Elmham Service Station | -£22.42 | -£25.96 | -£15.50 |
200 | 102528 | Dehn Services | £0.00 | £0.00 | -£0.78 |
201 | 102529 | M Morgan Motor Engineer Ltd | £8.99 | -£7.09 | -£14.50 |
202 | 102530 | Parkside Garage | -£1.93 | -£3.09 | -£1.30 |
203 | 102531 | Langor Bridge Garage | £0.00 | £0.00 | -£2.48 |
204 | 102532 | West Raynham Auto Clinic | -£6.97 | £0.00 | -£2.51 |
205 | 102533 | Colkirk Motors | -£0.46 | £0.00 | -£2.53 |
206 | 102534 | Howes Of Fakenham | £0.00 | £0.00 | £0.00 |
207 | 102535 | Coburn Vehicle Systems Ltd | -£3.69 | £0.00 | -£1.82 |
208 | 102536 | Major Tyres | -£0.18 | -£4.31 | -£2.30 |
209 | 102537 | Bell Motors | -£1.21 | £0.00 | -£0.37 |
210 | 102539 | Fakenham Auto Electrical | £0.00 | £0.00 | £0.00 |
211 | 102542 | R & M Eke Motor Engineers | £9.29 | -£1.66 | £14.70 |
212 | 102544 | Nick Massingham | -£3.85 | -£1.68 | -£1.57 |
213 | 102545 | Braggs Of Briston Ltd | -£1.43 | £0.00 | -£7.98 |
214 | 102546 | R And K Autos | £0.00 | £0.00 | £0.00 |
215 | 102548 | R M C | -£6.88 | -£20.04 | -£15.83 |
216 | 102553 | Aerolite Garage | £2.88 | £0.00 | £33.07 |
217 | 102554 | P J's | £0.00 | £0.00 | £0.00 |
218 | 102557 | Hjc Ltd (Home James) | £0.00 | -£0.10 | -£12.34 |
<tbody>
</tbody>
My lists Look Like this
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | SPP Top 50 | MPP Top 50 | HCL Top 20 | |||||||||||
2 | # | Account Number | Account Name | Estimated Reabate | # | Account Number | Account Name | Estimated Reabate | # | Account Number | Account Name | Estimated Reabate | ||
3 | 1 | 138066 | Stuart Wright Transport | £98.39 | 1 | 102676 | D C Last Vehicle Sales Ltd | £218.72 | 1 | 102329 | Cooper Norwich | £245.93 | ||
4 | 2 | 102697 | Brown & Sons (Loddon) Ltd | £97.08 | 2 | 187455 | Stebbings Car Centre Ltd | £135.44 | 2 | 102526 | Leeders Accident Centre Ltd | £142.10 | ||
5 | 3 | 102586 | Morters Garage | £93.44 | 3 | 106785 | L D AUTOVOGUE LTD | £95.78 | 3 | 102484 | Jamie's Accident Repair Centr | £84.38 | ||
6 | 4 | 212518 | Das Auto Norwich | £79.05 | 4 | 178478 | AFFORDABLE CARS LTD | £82.91 | 4 | 171007 | M J AUTOPARTS | £73.79 | ||
7 | 5 | 102703 | Yelverton Garage Ltd | £65.46 | 5 | 102329 | Cooper Norwich | £76.64 | 5 | 102703 | Yelverton Garage Ltd | £73.71 | ||
8 | 6 | 181692 | ANGLIA AUTOGAS | £62.59 | 6 | 102502 | W W Autos | £71.38 | 6 | 223411 | JO JO'S | £70.26 | ||
9 | 7 | 153600 | Wright Part Ltd | £61.91 | 7 | 250908 | K G B Transport | £57.60 | 7 | 245148 | Norfolk Sports | £68.15 | ||
10 | 8 | 156717 | Kings Lynn VW Centre Limited | £56.57 | 8 | 145204 | IMPERIAL COMMERICAL | £44.82 | 8 | 102359 | Motor Bodies | £48.10 | ||
11 | 9 | 185523 | Canary Guttering Service | £52.11 | 9 | 189460 | Auto-Tec | £34.45 | 9 | 235290 | Top Coat | £47.77 | ||
12 | 10 | 206003 | Loddon Motortec | £47.55 | 10 | 245253 | A B Autos | £28.31 | 10 | 102510 | Terry's Engineering Services | £43.25 | ||
13 | 11 | 215466 | Station Road Garage (Heacham) | £41.42 | 11 | 106493 | Goldline Taxis | £24.92 | 11 | 106785 | L D AUTOVOGUE LTD | £37.51 | ||
14 | 12 | 242567 | Smart Refinish | £38.51 | 12 | 156511 | LANGOR BRIDGE AUTOS | £23.49 | 12 | 232979 | Hillcrest Vehicle Solutions L | £36.37 | ||
15 | 13 | 102494 | M Rivett Mtrs | £37.82 | 13 | 196253 | Caters Service Station | £23.47 | 13 | 102553 | Aerolite Garage | £33.07 | ||
16 | 14 | 241797 | Repps Garage (Norfolk) Ltd | £37.15 | 14 | 122906 | Wensum Motor Company | £22.94 | 14 | 213554 | A & S Bodyfix Ltd | £22.15 | ||
17 | 15 | 245343 | Hagstrom Saab | £36.73 | 15 | 102586 | Morters Garage | £22.67 | 15 | 102297 | P A R Motors | £21.45 | ||
18 | 16 | 181757 | CAR AUTOHAUS | £35.71 | 16 | 154086 | EMG Motor Group Ltd | £16.23 | 16 | 226541 | C S N Auto's Ltd. | £21.16 | ||
19 | 17 | 112559 | M K Repairs Limited | £34.67 | 17 | 218037 | Rob Palmer AutoTech | £15.29 | 17 | 238208 | Jason Potts | £18.99 | ||
20 | 18 | 175362 | A V S | £34.31 | 18 | 225778 | Colin Hughes Auto Services | £14.38 | 18 | 180100 | Simon Emms | £17.91 | ||
21 | 19 | 239116 | Tec 41 ltd | £30.87 | 19 | 147271 | JOHN AYERS CARS | £10.81 | 19 | 106829 | Airport Carcare | £16.85 | ||
22 | 20 | 106233 | A & M Motors | £29.40 | 20 | 198022 | Auto-Mate | £7.42 | 20 | 204534 | Can do Autocentres Ltd | £16.52 |
<tbody>
</tbody>
obviously this is only a sample of the data as there are thousands of lines.
your help as always is greatly appreciated.
Dave