Excluding Cells From an Array!

[AstToTheRegionalMGR]

Hi,
I have a list of my team of 12 people, and next to it their total sales, among other data. I want to create, on a separate sheet, a leaderboard list. So it will just automatically sort everyone from most sales to least sales. The problem I'm having is if two people have the same sales, it will only report the person who is higher on the original list, I don't want this. I should also mention the original list cannot be re-ordered.

The formulas I'm currently using:
For top salesman: =INDEX($B:$B,MATCH(MAX($F$2:$F$13),$F:$F,0))
For the next 10: =(INDEX($B:$B,MATCH(LARGE($F:$F,2),$F:$F,0))) *Note- I have changed the large to 3, 4, 5, etc for each row
For the lowest salesman: =(INDEX($B:$B,MATCH(SMALL($F:$F,1),$F:$F,0)))

I imagine I'll need to do something like =IF(A2=A1, find second in lookup list, use original formula)

Thank you!

[FlameRetired]

Without an uploaded workbook I'll hazard some guesses.

The attached workbook w/ examples have the following formulas:

Edit: BTW This solution works from right to left (column H then column G); admittedly a bit counter intuitive, but in cases like this it works.

In H2 enter and fill down:

Please Login or Register  to view this content.

In G2 array-enter this:

Please Login or Register  to view this content.

Array enter means the formula is committed with Ctrl + Shift + Enter and not simply enter.
Fill down.


Is this useful?

[AstToTheRegionalMGR]

I can't seem to get the array formula right. I'm hitting ctrl+shift+enter but it's just displaying the array formula as text

[FlameRetired]

Is it possible that Show Formulas is turned on?

[AstToTheRegionalMGR]

No, it is showing non-array formulas. It's just the array formula I'm struggling with

[FlameRetired]

Can you post a sample workbook that shows us what is happening? It will help us identify other possible causes.

[newdoverman]

Perhaps this will help you solve your problem. I used a helper column on sheet1 to rank the sales with this formula entered in Sheet1 H2 and copied down. This took the ranking and added 1 to any ties so that tied sales could be handled as if they were uniquely ranked. In the SalseRanking worksheet the tied sales are brought together in the order that they are found in, in the main data on Sheet1.

Please Login or Register  to view this content.