Top & Bottom figures to bring from Data to Result sheet

[Zubair]

Hi all,

I have a list of 100 customer contained sales volumes for current & last year and a percentage variance column in Data sheet, from this I want Top & Bottom customers with both year sales based on percentage column. Cell D2 is showing the number of Top and Bottom customers view required it may change from 10 to 20 or 5.

Please help to share formula.

Data Sheet

Customer 86	5,673	4,763	19.09%​
Customer 88	4,981	4,210	18.33%​
Customer 90	5,758	4,931	16.75%​
Customer 91	4,973	4,283	16.11%​
Customer 89	4,200	3,632	15.63%​
Customer 76	4,288	3,709	15.61%​
Customer 92	4,513	3,922	15.08%​
Customer 72	4,182	3,646	14.70%​
Customer 84	4,666	4,123	13.17%​
Customer 68	4,912	4,374	12.31%​
Customer 27	3,661	3,266	12.11%​
Customer 56	3,941	3,518	12.03%​
Customer 55	4,378	3,916	11.80%​
Customer 19	3,846	3,471	10.80%​
Customer 83	4,228	3,827	10.48%​
Customer 4	4,089	3,708	10.27%​
Customer 95	4,179	3,791	10.24%​
Customer 96	3,624	3,291	10.12%​
Customer 73	4,019	3,654	9.99%​
Customer 16	3,379	3,078	9.78%​
Customer 97	5,446	4,967	9.66%​
Customer 20	3,706	3,383	9.55%​
Customer 99	4,102	3,745	9.55%​
Customer 18	3,377	3,085	9.45%​
Customer 98	3,681	3,382	8.86%​
Customer 6	4,169	3,831	8.84%​
Customer 34	3,430	3,156	8.68%​
Customer 47	3,454	3,178	8.68%​
Customer 77	3,619	3,334	8.55%​
Customer 1	3,177	2,933	8.33%​
Customer 66	3,935	3,643	8.04%​
Customer 58	3,673	3,404	7.90%​
Customer 87	4,012	3,725	7.72%​
Customer 78	3,492	3,242	7.69%​
Customer 67	3,471	3,231	7.44%​
Customer 64	3,410	3,182	7.16%​
Customer 12	3,458	3,230	7.07%​
Customer 29	3,390	3,172	6.87%​
Customer 23	3,237	3,035	6.65%​
Customer 25	3,871	3,632	6.57%​
Customer 15	2,953	2,773	6.49%​
Customer 61	3,146	2,963	6.17%​
Customer 93	4,383	4,131	6.09%​
Customer 53	3,612	3,418	5.68%​
Customer 100	4,059	3,847	5.49%​
Customer 52	2,870	2,722	5.46%​
Customer 17	2,472	2,344	5.45%​
Customer 24	3,596	3,418	5.21%​
Customer 32	3,113	2,960	5.20%​
Customer 50	3,238	3,084	5.01%​
Customer 75	3,839	3,672	4.54%​
Customer 3	3,632	3,483	4.27%​
Customer 69	3,365	3,236	4.00%​
Customer 85	2,881	2,791	3.21%​
Customer 7	3,623	3,521	2.90%​
Customer 5	3,332	3,242	2.78%​
Customer 80	3,822	3,728	2.51%​
Customer 11	3,139	3,062	2.48%​
Customer 36	3,345	3,268	2.36%​
Customer 71	2,791	2,730	2.23%​
Customer 82	3,856	3,772	2.22%​
Customer 79	3,246	3,178	2.15%​
Customer 41	2,938	2,890	1.65%​
Customer 42	2,742	2,699	1.59%​
Customer 2	3,277	3,226	1.58%​
Customer 39	3,474	3,422	1.53%​
Customer 65	3,287	3,245	1.30%​
Customer 62	3,150	3,130	0.63%​
Customer 14	2,768	2,755	0.46%​
Customer 74	3,183	3,171	0.38%​
Customer 33	2,664	2,665	-0.05%​
Customer 44	3,102	3,108	-0.19%​
Customer 59	2,795	2,806	-0.37%​
Customer 28	2,895	2,908	-0.44%​
Customer 9	3,118	3,134	-0.53%​
Customer 22	2,823	2,838	-0.54%​
Customer 45	2,698	2,717	-0.71%​
Customer 10	2,844	2,866	-0.77%​
Customer 37	2,953	3,002	-1.63%​
Customer 31	2,902	2,973	-2.39%​
Customer 81	3,275	3,370	-2.82%​
Customer 54	2,324	2,395	-2.96%​
Customer 94	3,550	3,664	-3.11%​
Customer 40	2,800	2,898	-3.38%​
Customer 8	3,215	3,336	-3.61%​
Customer 46	2,799	2,908	-3.78%​
Customer 43	2,534	2,641	-4.04%​
Customer 51	2,779	2,921	-4.85%​
Customer 70	2,637	2,774	-4.94%​
Customer 49	2,680	2,825	-5.11%​
Customer 48	2,628	2,772	-5.19%​
Customer 30	3,103	3,277	-5.31%​
Customer 63	2,894	3,062	-5.49%​
Customer 13	2,708	2,871	-5.67%​
Customer 21	2,591	2,765	-6.31%​
Customer 35	2,628	2,808	-6.42%​
Customer 38	2,434	2,628	-7.36%​
Customer 57	2,469	2,676	-7.74%​
Customer 60	2,315	2,515	-7.96%​
Customer 26	2,798	3,065	-8.71%​

Result Sheet

Top & Bottom		​	10
Customer No.	Current year	Last year	% Diff
Customer 86	5,673	4,763	19.09%​
Customer 88	4,981	4,210	18.33%​
Customer 90	5,758	4,931	16.75%​
Customer 91	4,973	4,283	16.11%​
Customer 89	4,200	3,632	15.63%​
Customer 76	4,288	3,709	15.61%​
Customer 92	4,513	3,922	15.08%​
Customer 72	4,182	3,646	14.70%​
Customer 84	4,666	4,123	13.17%​
Customer 68	4,912	4,374	12.31%​
Customer 48	2,628	2,772	-5.19%​
Customer 30	3,103	3,277	-5.31%​
Customer 63	2,894	3,062	-5.49%​
Customer 13	2,708	2,871	-5.67%​
Customer 21	2,591	2,765	-6.31%​
Customer 35	2,628	2,808	-6.42%​
Customer 38	2,434	2,628	-7.36%​
Customer 57	2,469	2,676	-7.74%​
Customer 60	2,315	2,515	-7.96%​
Customer 26	2,798	3,065	-8.71%​

 

[richh]

You can do so using the LARGE, SMALL, INDIRECT, and MATCH functions.

Results:

Formulas:

Do note there will be an issue if there are customers with exact values; not sure how to clear it up.

Cell G2 (drag down to G11): =LARGE($B$2:$B$101,F2)
Cell H2 (drag down to H11): =INDIRECT("A" & MATCH(G2,$B$1:$B$101,0))

Cell K2 (drag down): =SMALL($B$2:$B$101,J2)
Cell L2 (drag down): =INDIRECT("A" & MATCH(K2,$B$2:$B$101,0))