Top 50 List using Formulas

[davewatson86]

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

	A	B	O	P	Q
Stephan Bmw
J Parker
Leeders Accident Centre Ltd
North Elmham Service Station
Dehn Services
M Morgan Motor Engineer Ltd
Parkside Garage
Langor Bridge Garage
West Raynham Auto Clinic
Colkirk Motors
Howes Of Fakenham
Coburn Vehicle Systems Ltd
Major Tyres
Bell Motors
Fakenham Auto Electrical
R & M Eke Motor Engineers
Nick Massingham
Braggs Of Briston Ltd
R And K Autos
R M C
Aerolite Garage
P J's
Hjc Ltd (Home James)

<tbody>
[TD="align: center"]196[/TD]
[TD="align: right"]102521[/TD]

[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]-£1.92[/TD]

[TD="align: center"]197[/TD]
[TD="align: right"]102525[/TD]

[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]-£0.37[/TD]

[TD="align: center"]198[/TD]
[TD="align: right"]102526[/TD]

[TD="align: right"]-£0.48[/TD]
[TD="align: right"]£4.35[/TD]
[TD="align: right"]£142.10[/TD]

[TD="align: center"]199[/TD]
[TD="align: right"]102527[/TD]

[TD="align: right"]-£22.42[/TD]
[TD="align: right"]-£25.96[/TD]
[TD="align: right"]-£15.50[/TD]

[TD="align: center"]200[/TD]
[TD="align: right"]102528[/TD]

[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]-£0.78[/TD]

[TD="align: center"]201[/TD]
[TD="align: right"]102529[/TD]

[TD="align: right"]£8.99[/TD]
[TD="align: right"]-£7.09[/TD]
[TD="align: right"]-£14.50[/TD]

[TD="align: center"]202[/TD]
[TD="align: right"]102530[/TD]

[TD="align: right"]-£1.93[/TD]
[TD="align: right"]-£3.09[/TD]
[TD="align: right"]-£1.30[/TD]

[TD="align: center"]203[/TD]
[TD="align: right"]102531[/TD]

[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]-£2.48[/TD]

[TD="align: center"]204[/TD]
[TD="align: right"]102532[/TD]

[TD="align: right"]-£6.97[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]-£2.51[/TD]

[TD="align: center"]205[/TD]
[TD="align: right"]102533[/TD]

[TD="align: right"]-£0.46[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]-£2.53[/TD]

[TD="align: center"]206[/TD]
[TD="align: right"]102534[/TD]

[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]

[TD="align: center"]207[/TD]
[TD="align: right"]102535[/TD]

[TD="align: right"]-£3.69[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]-£1.82[/TD]

[TD="align: center"]208[/TD]
[TD="align: right"]102536[/TD]

[TD="align: right"]-£0.18[/TD]
[TD="align: right"]-£4.31[/TD]
[TD="align: right"]-£2.30[/TD]

[TD="align: center"]209[/TD]
[TD="align: right"]102537[/TD]

[TD="align: right"]-£1.21[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]-£0.37[/TD]

[TD="align: center"]210[/TD]
[TD="align: right"]102539[/TD]

[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]

[TD="align: center"]211[/TD]
[TD="align: right"]102542[/TD]

[TD="align: right"]£9.29[/TD]
[TD="align: right"]-£1.66[/TD]
[TD="align: right"]£14.70[/TD]

[TD="align: center"]212[/TD]
[TD="align: right"]102544[/TD]

[TD="align: right"]-£3.85[/TD]
[TD="align: right"]-£1.68[/TD]
[TD="align: right"]-£1.57[/TD]

[TD="align: center"]213[/TD]
[TD="align: right"]102545[/TD]

[TD="align: right"]-£1.43[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]-£7.98[/TD]

[TD="align: center"]214[/TD]
[TD="align: right"]102546[/TD]

[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]

[TD="align: center"]215[/TD]
[TD="align: right"]102548[/TD]

[TD="align: right"]-£6.88[/TD]
[TD="align: right"]-£20.04[/TD]
[TD="align: right"]-£15.83[/TD]

[TD="align: center"]216[/TD]
[TD="align: right"]102553[/TD]

[TD="align: right"]£2.88[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£33.07[/TD]

[TD="align: center"]217[/TD]
[TD="align: right"]102554[/TD]

[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£0.00[/TD]

[TD="align: center"]218[/TD]
[TD="align: right"]102557[/TD]

[TD="align: right"]£0.00[/TD]
[TD="align: right"]-£0.10[/TD]
[TD="align: right"]-£12.34[/TD]

</tbody>

My lists Look Like this

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
Brown & Sons (Loddon) Ltd	Stebbings Car Centre Ltd	Leeders Accident Centre Ltd
Das Auto Norwich	AFFORDABLE CARS LTD	M J AUTOPARTS
ANGLIA AUTOGAS	W W Autos	JO JO'S
Kings Lynn VW Centre Limited	IMPERIAL COMMERICAL	Motor Bodies
Loddon Motortec	A B Autos	Terry's Engineering Services
Smart Refinish	LANGOR BRIDGE AUTOS	Hillcrest Vehicle Solutions L
Repps Garage (Norfolk) Ltd	Wensum Motor Company	A & S Bodyfix Ltd
CAR AUTOHAUS	EMG Motor Group Ltd	C S N Auto's Ltd.
A V S	Colin Hughes Auto Services	Simon Emms
A & M Motors	Auto-Mate	Can do Autocentres Ltd

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC, align: center"]SPP Top 50[/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: center"]MPP Top 50[/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: center"]HCL Top 20[/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #DDEBF7, align: center"]#[/TD]
[TD="bgcolor: #DDEBF7"]Account Number[/TD]
[TD="bgcolor: #DDEBF7"]Account Name[/TD]
[TD="bgcolor: #DDEBF7"]Estimated Reabate[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]#[/TD]
[TD="bgcolor: #DDEBF7"]Account Number[/TD]
[TD="bgcolor: #DDEBF7"]Account Name[/TD]
[TD="bgcolor: #DDEBF7"]Estimated Reabate[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]#[/TD]
[TD="bgcolor: #DDEBF7"]Account Number[/TD]
[TD="bgcolor: #DDEBF7"]Account Name[/TD]
[TD="bgcolor: #DDEBF7"]Estimated Reabate[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #DDEBF7, align: center"]1[/TD]
[TD="bgcolor: #F2F2F2, align: center"]138066[/TD]
[TD="bgcolor: #F2F2F2"]Stuart Wright Transport[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£98.39[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]1[/TD]
[TD="bgcolor: #F2F2F2, align: center"]102676[/TD]
[TD="bgcolor: #F2F2F2"]D C Last Vehicle Sales Ltd[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£218.72[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]1[/TD]
[TD="bgcolor: #F2F2F2, align: center"]102329[/TD]
[TD="bgcolor: #F2F2F2"]Cooper Norwich[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£245.93[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #DDEBF7, align: center"]2[/TD]
[TD="align: center"]102697[/TD]

[TD="align: center"]£97.08[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]2[/TD]
[TD="align: center"]187455[/TD]

[TD="align: center"]£135.44[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]2[/TD]
[TD="align: center"]102526[/TD]

[TD="align: center"]£142.10[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #DDEBF7, align: center"]3[/TD]
[TD="bgcolor: #F2F2F2, align: center"]102586[/TD]
[TD="bgcolor: #F2F2F2"]Morters Garage[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£93.44[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]3[/TD]
[TD="bgcolor: #F2F2F2, align: center"]106785[/TD]
[TD="bgcolor: #F2F2F2"]L D AUTOVOGUE LTD[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£95.78[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]3[/TD]
[TD="bgcolor: #F2F2F2, align: center"]102484[/TD]
[TD="bgcolor: #F2F2F2"]Jamie's Accident Repair Centr[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£84.38[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #DDEBF7, align: center"]4[/TD]
[TD="align: center"]212518[/TD]

[TD="align: center"]£79.05[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]4[/TD]
[TD="align: center"]178478[/TD]

[TD="align: center"]£82.91[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]4[/TD]
[TD="align: center"]171007[/TD]

[TD="align: center"]£73.79[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #DDEBF7, align: center"]5[/TD]
[TD="bgcolor: #F2F2F2, align: center"]102703[/TD]
[TD="bgcolor: #F2F2F2"]Yelverton Garage Ltd[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£65.46[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]5[/TD]
[TD="bgcolor: #F2F2F2, align: center"]102329[/TD]
[TD="bgcolor: #F2F2F2"]Cooper Norwich[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£76.64[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]5[/TD]
[TD="bgcolor: #F2F2F2, align: center"]102703[/TD]
[TD="bgcolor: #F2F2F2"]Yelverton Garage Ltd[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£73.71[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #DDEBF7, align: center"]6[/TD]
[TD="align: center"]181692[/TD]

[TD="align: center"]£62.59[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]6[/TD]
[TD="align: center"]102502[/TD]

[TD="align: center"]£71.38[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]6[/TD]
[TD="align: center"]223411[/TD]

[TD="align: center"]£70.26[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #DDEBF7, align: center"]7[/TD]
[TD="bgcolor: #F2F2F2, align: center"]153600[/TD]
[TD="bgcolor: #F2F2F2"]Wright Part Ltd[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£61.91[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]7[/TD]
[TD="bgcolor: #F2F2F2, align: center"]250908[/TD]
[TD="bgcolor: #F2F2F2"]K G B Transport[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£57.60[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]7[/TD]
[TD="bgcolor: #F2F2F2, align: center"]245148[/TD]
[TD="bgcolor: #F2F2F2"]Norfolk Sports[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£68.15[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #DDEBF7, align: center"]8[/TD]
[TD="align: center"]156717[/TD]

[TD="align: center"]£56.57[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]8[/TD]
[TD="align: center"]145204[/TD]

[TD="align: center"]£44.82[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]8[/TD]
[TD="align: center"]102359[/TD]

[TD="align: center"]£48.10[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #DDEBF7, align: center"]9[/TD]
[TD="bgcolor: #F2F2F2, align: center"]185523[/TD]
[TD="bgcolor: #F2F2F2"]Canary Guttering Service[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£52.11[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]9[/TD]
[TD="bgcolor: #F2F2F2, align: center"]189460[/TD]
[TD="bgcolor: #F2F2F2"]Auto-Tec[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£34.45[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]9[/TD]
[TD="bgcolor: #F2F2F2, align: center"]235290[/TD]
[TD="bgcolor: #F2F2F2"]Top Coat[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£47.77[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #DDEBF7, align: center"]10[/TD]
[TD="align: center"]206003[/TD]

[TD="align: center"]£47.55[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]10[/TD]
[TD="align: center"]245253[/TD]

[TD="align: center"]£28.31[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]10[/TD]
[TD="align: center"]102510[/TD]

[TD="align: center"]£43.25[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #DDEBF7, align: center"]11[/TD]
[TD="bgcolor: #F2F2F2, align: center"]215466[/TD]
[TD="bgcolor: #F2F2F2"]Station Road Garage (Heacham)[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£41.42[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]11[/TD]
[TD="bgcolor: #F2F2F2, align: center"]106493[/TD]
[TD="bgcolor: #F2F2F2"]Goldline Taxis[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£24.92[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]11[/TD]
[TD="bgcolor: #F2F2F2, align: center"]106785[/TD]
[TD="bgcolor: #F2F2F2"]L D AUTOVOGUE LTD[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£37.51[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #DDEBF7, align: center"]12[/TD]
[TD="align: center"]242567[/TD]

[TD="align: center"]£38.51[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]12[/TD]
[TD="align: center"]156511[/TD]

[TD="align: center"]£23.49[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]12[/TD]
[TD="align: center"]232979[/TD]

[TD="align: center"]£36.37[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #DDEBF7, align: center"]13[/TD]
[TD="bgcolor: #F2F2F2, align: center"]102494[/TD]
[TD="bgcolor: #F2F2F2"]M Rivett Mtrs[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£37.82[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]13[/TD]
[TD="bgcolor: #F2F2F2, align: center"]196253[/TD]
[TD="bgcolor: #F2F2F2"]Caters Service Station[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£23.47[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]13[/TD]
[TD="bgcolor: #F2F2F2, align: center"]102553[/TD]
[TD="bgcolor: #F2F2F2"]Aerolite Garage[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£33.07[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #DDEBF7, align: center"]14[/TD]
[TD="align: center"]241797[/TD]

[TD="align: center"]£37.15[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]14[/TD]
[TD="align: center"]122906[/TD]

[TD="align: center"]£22.94[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]14[/TD]
[TD="align: center"]213554[/TD]

[TD="align: center"]£22.15[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #DDEBF7, align: center"]15[/TD]
[TD="bgcolor: #F2F2F2, align: center"]245343[/TD]
[TD="bgcolor: #F2F2F2"]Hagstrom Saab[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£36.73[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]15[/TD]
[TD="bgcolor: #F2F2F2, align: center"]102586[/TD]
[TD="bgcolor: #F2F2F2"]Morters Garage[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£22.67[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]15[/TD]
[TD="bgcolor: #F2F2F2, align: center"]102297[/TD]
[TD="bgcolor: #F2F2F2"]P A R Motors[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£21.45[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #DDEBF7, align: center"]16[/TD]
[TD="align: center"]181757[/TD]

[TD="align: center"]£35.71[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]16[/TD]
[TD="align: center"]154086[/TD]

[TD="align: center"]£16.23[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]16[/TD]
[TD="align: center"]226541[/TD]

[TD="align: center"]£21.16[/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #DDEBF7, align: center"]17[/TD]
[TD="bgcolor: #F2F2F2, align: center"]112559[/TD]
[TD="bgcolor: #F2F2F2"]M K Repairs Limited[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£34.67[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]17[/TD]
[TD="bgcolor: #F2F2F2, align: center"]218037[/TD]
[TD="bgcolor: #F2F2F2"]Rob Palmer AutoTech[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£15.29[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]17[/TD]
[TD="bgcolor: #F2F2F2, align: center"]238208[/TD]
[TD="bgcolor: #F2F2F2"]Jason Potts[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£18.99[/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #DDEBF7, align: center"]18[/TD]
[TD="align: center"]175362[/TD]

[TD="align: center"]£34.31[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]18[/TD]
[TD="align: center"]225778[/TD]

[TD="align: center"]£14.38[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]18[/TD]
[TD="align: center"]180100[/TD]

[TD="align: center"]£17.91[/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #DDEBF7, align: center"]19[/TD]
[TD="bgcolor: #F2F2F2, align: center"]239116[/TD]
[TD="bgcolor: #F2F2F2"]Tec 41 ltd[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£30.87[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]19[/TD]
[TD="bgcolor: #F2F2F2, align: center"]147271[/TD]
[TD="bgcolor: #F2F2F2"]JOHN AYERS CARS[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£10.81[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]19[/TD]
[TD="bgcolor: #F2F2F2, align: center"]106829[/TD]
[TD="bgcolor: #F2F2F2"]Airport Carcare[/TD]
[TD="bgcolor: #F2F2F2, align: center"]£16.85[/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #DDEBF7, align: center"]20[/TD]
[TD="align: center"]106233[/TD]

[TD="align: center"]£29.40[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]20[/TD]
[TD="align: center"]198022[/TD]

[TD="align: center"]£7.42[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: center"]20[/TD]
[TD="align: center"]204534[/TD]

[TD="align: center"]£16.52
[/TD]

</tbody>

obviously this is only a sample of the data as there are thousands of lines.

your help as always is greatly appreciated.

Dave

 

[Eric W]

Can the customer show up in column B more than once? I assume that columns O, P, Q are the estimated rebates for each group? And you want to base the inclusion in the result lists based on the sum of the totals in those columns for all the entries for a given customer in column B?

And I haven't really started designing anything yet, but it looks to be fairly complicated for formulas. Would you be amenable to helper columns if that would help?

 

[davewatson86]

The customer in column a and b will not show up more than once, helper columns is no problem as I can just hide them off to the side.
Thank you for your help ?

 

[Eric W]

I was working through the steps I'd need to do to accomplish this, and I ended up adding the helper columns to the Data sheet. Consider this:

Book1
	A	B	O	P	Q	R	S	T	U	V	W	X
1
2			SPP	MPP	HCL		SPP	MPP	HCL	SPP	MPP	HCL
3	102521	Stephan Bmw	0	0	-1.92
4	102525	J Parker	0	0	-0.37
5	102526	Leeders Accident Centre Ltd	-0.48	4.35	142.1				142.1			1
6	102527	North Elmham Service Station	-22.42	-25.96	-15.5
7	102528	Dehn Services	0	0	-0.78
8	102529	M Morgan Motor Engineer Ltd	8.99	-7.09	-14.5		8.99			1
9	102530	Parkside Garage	-1.93	-3.09	-1.3
10	102531	Langor Bridge Garage	0	0	-2.48
11	102532	West Raynham Auto Clinic	-6.97	0	-2.51
12	102533	Colkirk Motors	-0.46	0	-2.53
13	102534	Howes Of Fakenham	0	0	0
14	102535	Coburn Vehicle Systems Ltd	-3.69	0	-1.82
15	102536	Major Tyres	-0.18	-4.31	-2.3
16	102537	Bell Motors	-1.21	0	-0.37
17	102539	Fakenham Auto Electrical	0	0	0
18	102542	R & M Eke Motor Engineers	9.29	-1.66	14.7				14.7			3
19	102544	Nick Massingham	-3.85	-1.68	-1.57
20	102545	Braggs Of Briston Ltd	-1.43	0	-7.98
21	102546	R And K Autos	0	0	0
22	102548	R M C	-6.88	-20.04	-15.83
23	102553	Aerolite Garage	2.88	0	33.07				33.07			2
24	102554	P J's	0	0	0
25	102557	Hjc Ltd (Home James)	0	-0.1	-12.34
26
Customer Data

Cell Formulas
Range	Formula
S3	=IF(AND(O3>0,MAX(O3:Q3)=O3),O3,"")
T3	=IF(AND(P3>0,MAX(O3:Q3)=P3,S3=""),P3,"")
U3	=IF(AND(Q3>0,MAX(O3:Q3)=Q3,S3="",T3=""),Q3,"")
V3	=IF(S3<>"",RANK(S3,S$3:S$40),"")

The S, T, and U formulas decide which column (if any) the rebate should go in. Then the V3 formula, which you copy across to X3 and down as far as needed, gives the ranking. Then your Top 50 lists only have to do an INDEX(MATCH on the ranking number to find the account number, name, and amount. I'll see if I can consolidate this a bit.

 

[davewatson86]

Many thanks Eric W this worked a treat and has saved me heaps of time, absolute legend

Regards
Dave

 

[Eric W]

Glad it works for you!

I spent some time trying to get this a bit simpler, but everything I tried required longer, more complicated, and more calculation intensive formulas. This takes extra columns, but it's pretty easy to follow and is efficient.