I have an excel file with 4 Column, need to populate a separte excel sheet with Top 10 customer from column a who have top 10 values in colum b

[makvisions]

Hello,

I have an excel file with approx. 800 customers in column a, column b is the month it represent, column c is monthly expense
now I want to populate a separate sheet with Top 10 customers in column a and corresponding expense in column b

Below is the sample data, help is appreciated.





[TABLE="width: 475"]
<tbody>[TR]
[TD="align: center"]Sr. #[/TD]
[TD="align: center"]Customer [/TD]
[TD="align: center"]Expense Total [/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Customer 1[/TD]
[TD="align: center"]$48[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Customer 2[/TD]
[TD="align: center"]$5,250[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Customer 3[/TD]
[TD="align: center"]$1,732[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Customer 4[/TD]
[TD="align: center"]$36,157[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Customer 5[/TD]
[TD="align: center"]$12,479[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Customer 6[/TD]
[TD="align: center"]$4,828[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Customer 7[/TD]
[TD="align: center"]$899[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Customer 8[/TD]
[TD="align: center"]$11,718[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Customer 9[/TD]
[TD="align: center"]$857[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Customer 10[/TD]
[TD="align: center"]$26,749[/TD]
[/TR]
[TR]
[TD="align: center"]795[/TD]
[TD="align: center"]Customer 795[/TD]
[TD="align: center"]$16,962[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[63falcondude]

Assuming that there are no duplicate expenses, try this:

Excel 2010
	A	B	C
1	Customer	Month	Expense Total
2	Customer 1		$48
3	Customer 2		$5,250
4	Customer 3		$1,732
5	Customer 4		$36,157
6	Customer 5		$12,479
7	Customer 6		$4,828
8	Customer 7		$899
9	Customer 8		$11,718
10	Customer 9		$857
11	Customer 10		$26,749
12	Customer 795		$16,962
Sheet1

Excel 2010
	A	B
1	Customer	Expense Total
2	Customer 4	$36,157
3	Customer 10	$26,749
4	Customer 795	$16,962
5	Customer 5	$12,479
6	Customer 8	$11,718
7	Customer 2	$5,250
8	Customer 6	$4,828
9	Customer 3	$1,732
10	Customer 7	$899
11	Customer 9	$857
Sheet2

Cell Formulas
Range	Formula
A2	=INDEX(Sheet1!A:A,MATCH(B2,Sheet1!C:C,0))
B2	=LARGE(Sheet1!$C$2:$C$12,ROWS($A$1:$A1))

 

[63falcondude]

If duplicate expense amounts are possible, use this in A2

=INDEX(Sheet1!A$2:A$12,SMALL(IF(Sheet1!C$2:C$12=B2,ROW(Sheet1!C$2:C$12)-(ROW(Sheet1!C$2)-1)),COUNTIF(B$2:B2,B2))) Ctrl Shift Enter

 

[makvisions]

Super, worked like a charm

 

[makvisions]

the problem is solved, however when I try to apply to my situation I think i need to expand this formula,

my source is a pivot table which month, division, customer and monthly expense for aprox. 800 customers,

now I want to populate separate tab for each division with top 10 customer, basically every month i move one column further to populate that month data

here is sample data

[TABLE="width: 577"]
<tbody>[TR]
[TD]

Month ​

[/TD]
[TD]

Divison ​

[/TD]
[TD]

Customer ​

[/TD]
[TD]

Total​

[/TD]
[/TR]
[TR]
[TD="align: right"]

1/1/2017​

[/TD]
[TD]

A​

[/TD]
[TD]

Customer 1​

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

$1,069​

[/TD]
[/TR]
[TR]
[TD="align: right"]

2/1/2017​

[/TD]
[TD]

B ​

[/TD]
[TD]

Customer 2​

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

$277​

[/TD]
[/TR]
[TR]
[TD="align: right"]

3/1/2017​

[/TD]
[TD]

C​

[/TD]
[TD]

Customer 3​

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

$644​

[/TD]
[/TR]
[TR]
[TD="align: right"]

4/1/2017​

[/TD]
[TD]

D​

[/TD]
[TD]

Customer 4​

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

$490​

[/TD]
[/TR]
[TR]
[TD="align: right"]

5/1/2017​

[/TD]
[TD]

E​

[/TD]
[TD]

Customer 5​

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

$13,077​

[/TD]
[/TR]
[TR]
[TD="align: right"]

1/1/2017​

[/TD]
[TD]

A​

[/TD]
[TD]

Customer 1​

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

$2,062​

[/TD]
[/TR]
[TR]
[TD="align: right"]

2/1/2017​

[/TD]
[TD]

B ​

[/TD]
[TD]

Customer 2​

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

$324​

[/TD]
[/TR]
[TR]
[TD="align: right"]

3/1/2017​

[/TD]
[TD]

C​

[/TD]
[TD]

Customer 3​

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

$123​

[/TD]
[/TR]
[TR]
[TD="align: right"]

4/1/2017​

[/TD]
[TD]

D​

[/TD]
[TD]

Customer 4​

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

$2,807​

[/TD]
[/TR]
[TR]
[TD="align: right"]

5/1/2017​

[/TD]
[TD]

E​

[/TD]
[TD]

Customer 5​

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

$1,031​

[/TD]
[/TR]
</tbody>[/TABLE]