Lowest Code First, Followed By Name and Dollar Amount

[triv]

Hi,
*
I have a list that is constantly updated with “codes”, names, and dollar amounts. I am trying to make a list of the top five loans by dollar amounts based on the lowest code, first.
*
For example, if there are seven code 1s in my list:
*
Code Name Amount
1 Tri. $1,000
1 Mike. $600
1 Ben. $250
1 Daniel. $900
1 Hector. $150F
1 Nick. $75
1 Matt. $850
2 Anu. $3,000
2 Steven. $200
2 Brian. $450
2 John. $375
2 Kevin. $225
*
than the first five code 1s with the highest dollar amounts get listed so my following result would look like this:
*
#. Name Amount Code
1 Tri. $1,000. 1
2 Daniel. $900. 1
3 Matt. $850. 1
4 Mike. $600. 1
5 Ben. $250. 1
*
For this, I used the following CSEs:
*
The Name column:
{=INDEX($B$1:$B$14,SMALL(IF($C$1:$C$14=G6,ROW($C$1:$C$14)),COUNTIF(G$6:G6,G6)))}
*
The Amount column: {=LARGE(IF($A$1:$A$14=H6,$C$1:$C$14),ROW(1:1))}
*
The Code column: {=SMALL(IF($A$1:$A$14<>0,$A$1:$A$14),1)}
*
*
The issue I have is that since this list gets updated daily, there may not be more than five code 1s at one time, therefore, let’s say Ben, Hector, and Nick disappear leaving four code 1s (Tri, Mike, Daniel, and Matt), I want the #5 spot to fill with the next lowest code, which would be: code 2, Anu, $3,000. My following result would look like this:
*
Intended Result:
*
#. Name. Amount Code
1 Tri. $1,000. 1
2 Daniel. $900. 1
3 Matt. $850. 1
4 Mike. $600. 1
5 Anu. $3,000. 2
*
I'm doing all of this on my iPhone. I would attach an example in an Excel file but my iPhone won't allow it. This is for work and for some reason, I can't access Excel Forum at work. Any help would be greatly appreciated.
*
Thank you,
Tri

[newdoverman]

Assuming that your data is in columns A, B, and C, enter the numbers from 1 to 6 in E2:E7.

In F2 enter this array formula (entered by Ctrl+Shift+Enter) =INDEX($B$2:$B$100,MATCH(G2,$C$2:$C$100,0)) and copy down to F7

In G2 enter this array formula (entered by Ctrl+Shift+Enter) =MAX(IF($A$1:$A$100=E2,$C$1:$C$100)) and copy down to G7. This will give you the largest entry for each code along with the associated name.

[triv]

Thanks for the reply, newdoverman. I tried your formulas and 3-6 displays #N/A for the name and $0 off dollar amounts.

[newdoverman]

I have attached an Excel file that used your data and some that I added. Also included in the file is an example to get the smallest value for each code and name.

[triv]

Hey newdoverman, what I would like is if three of the seven code 1s went away, then the #5 spot would be taken by Anu , $3,000 , code 2. The conditions I set in my array formulas are that the lowest code numbers are selected first then sorted by highest dollar amount of the lowest code numbers. My problem is that when there are not enough code 1s available, the #5 spot displays an #N/A.

[abubaniyan]

Try this.....but in this you have to add additional two columns...SEE THE FILE OF 10.7KB

[newdoverman]

I think that you should look into using the Advanced filter and filter your data into a new location or use VBA to hide the cells that you don't want visible or to move your results to a new location.

As I now understand your problem, you can't use functions to accomplish what you want.