Formula to grab the next highest number

I used a helper column to identify the records that matched your criteria. The formula used in the first worksheet J2 and filled down:

Formula:

Please Login or Register  to view this content.

I created a second worksheet called Extract and used the headers from the first worksheet.
Enter this in A2 and fill across and down:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

In the workbook, I also used a filter to show that the records could be filtered after applying the first formula.

I missed what you meant by the Agent Level.

That mean you need the second largest AND not 1?
1,1,5 => pick 5?
1,1,4,5=>pick 4?

Originally Posted by TexasBobcat

What I want is the next highest number after 1.

is confusing me.
Could you write down few samples as per my post in #7?

=IF(IFERROR(LARGE(IF($A$2:$A$5064=A2,IF($H$2:$H$5064=1,"",$H$2:$H$5064),""),2),LARGE(IF($A$2:$A$5064=A2,IF($H$2:$H$5064=1,"",$H$2:$H$5064),""),1))=H2,I2,"")

Ctrl-shift-enter

Does it work?

Originally Posted by TexasBobcat

WHere should the formula you just gave me go?

paste into J2, confirmed with Ctrl-shift-enter, then drag down.

In trying to solve the agent level, I came up against the situation where there were two agents of the same level and same policy. This either caused a double entry or caused an error to kill the whole thing. In addition when I thought that I had it beat, I was getting oddities with other policies of two agents.....grrr.

I think that this will fix the problem...as far as I can determine.
Enter in J2 of the first worksheet and fill down

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

I believe this will work. With two helper columns ... in column K this array-entered formula

Formula:

Please Login or Register  to view this content.

(Array-entering has already been covered.) Then in column L this non-array formula.

Formula:

Please Login or Register  to view this content.

And then in column M:U this non-array formula.

Formula:

Please Login or Register  to view this content.

Edit I failed to mention that Helper column L returns the next highest Agent Level number. Sorry.