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.

Sr. #	Customer	Expense Total
1	Customer 1	$48
2	Customer 2	$5,250
3	Customer 3	$1,732
4	Customer 4	$36,157
5	Customer 5	$12,479
6	Customer 6	$4,828
7	Customer 7	$899
8	Customer 8	$11,718
9	Customer 9	$857
10	Customer 10	$26,749
795	Customer 795	$16,962

<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

[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

Month ​	Divison ​	Customer ​	Total​
1/1/2017​	A​	Customer 1​	$1,069​
2/1/2017​	B ​	Customer 2​	$277​
3/1/2017​	C​	Customer 3​	$644​
4/1/2017​	D​	Customer 4​	$490​
5/1/2017​	E​	Customer 5​	$13,077​
1/1/2017​	A​	Customer 1​	$2,062​
2/1/2017​	B ​	Customer 2​	$324​
3/1/2017​	C​	Customer 3​	$123​
4/1/2017​	D​	Customer 4​	$2,807​
5/1/2017​	E​	Customer 5​	$1,031​

<tbody> </tbody>