CODEWITHSUNDEEP
excel-formula
Jtravis
Jtravis

Reputation: 3

In Excel list the Top 10 account balances from one column based on the data in another column

I have an excel spreadsheet that on Sheet 1 is a list of account numbers in column A, type of account in column B, the account balance in column c. On Sheet 2 I need a formula that will give me the top 10 account largest to smallest account balances based on the text in column B of sheet 1.

Example: I need the a formula that will give me the top x account balances that have SA in column B from sheet 1 and place that data in column C rows 4 thru 14 of Sheet 2.

Sheet 1 looks like this:

Col A   Col B  Col C  
Acct #  Type   Balance             
42196   SA     11,466.76 
18492   DD    115,312.42 
48143   SA     60,365.32 
48660   DD     75,236.96 
55465   SA      8,000.77 
77795   SA     30,508.24 
55863   SA    115,510.00 
19967   SA     70,853.68 
77863   SA      9,956.06 
29557   SA    105,147.61 
59512   SA     19,691.04 
7911    SA     10,002.20 
3819    SA     20,013.25 
5597    SA     73,207.91 
11070   SA     16,590.42 
10997   SA     19,679.72 
58999   SA     32,988.78 
20095   DD      4,509.71 
81708   SA      8,000.73 
32866   SA      4,823.26

Sheet 2 looks like this and I need the formula in Column C rows 4 thru 23

Col A                  Col C                 Col D
                       Balance               Name           
1st Top Account         
2nd Top Account         
3rd Top Account         
4th Top Account         
5th Top Account         
6th Top Account         
7th Top Account         
8th Top Account

Upvotes: 0

Views: 84

Answers (1)

Jacob Edmond
Jacob Edmond

Reputation: 808

You want to use AGGREGATE. Enter this in col C on the 1st Top Account line and drag down:

=AGGREGATE(14,6,Sheet1!C:C/(Sheet1!B:B="SA"),ROWS($A$1:A1))

Upvotes: 0

Related Questions