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

 

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