• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to differentiate between multiple similar results

Grumpy88

Grumpy88

Member
Hi all.

I need help creating a formula please, which will not get hung up only on the first instance of a number if there are multiple instances of that same number.

I have a list of names in column A and a list of scores in column B, and am trying to get a formula to list the scores from highest to lowest in a different workbook (and then display the corresponding name via an INDEX/MATCH combination). I am using the LARGE function to do the sorting, but find that if two people have the same score, it lists the same person (who achieved the score first) twice.

I suspect the answer may have something to do with incorporating a NOT and/or CHOOSE function, but I'm not really familiar with using those and am battling to get a workable solution.

Can anyone perhaps help? Thanks!
 
Various options, but a helper column next to the current rankings would make for the easiest solution: you would add an increment to each tied ranking and then use a normal INDEX MATCH with LARGE for the list.
 
Thanks. However, I'd rather not mess with the underlying source workbook, as that would introduce other complications. I also already have an incremental formula system in there for another purpose, so including another on top of that would probably mess that up.

Any straight formula approach on just the reporting workbook?
 
It wouldn't mess anything up - it would be a standalone formula that could be in a hidden column.

Please attach a sample workbook here if you want a formula.
 
Okay. It's taken me quite a bit of turning this way and that in trying to work this out, but it looks like I have it now:

A help column with a COUNTIF function wrapped in an IF function seems to have done the job.

Thanks very much!
 
Last edited:
Glad you got it sorted! :)

I would have been happy to show you how to do it on Monday, however.
 
Actually, I see there is still an issue. I've added a fixed incremental value (+0.05) in my formula to each score as from the second occurrence onwards. That works when there are two identical values, but I am still getting duplicate results returned when there are three or more identical scores.

How do I ensure that each repeat gets a different value in the helper column, so that the INDEX/MATCH reports accurately on all occurrences?

Thanks.
 
Have a look at this.

EDIT: I no longer seem able to upload workbooks. Give me a mo!
 
I'm sorry - can't seem to post an image either - the upload dialog's don't want to see any files on my computer!
 
In fact, you can dispense with helper column E altogether with this in F:

=INDEX($A$1:$A$8,MATCH(LARGE($C$1:$C$8,ROWS(A$1:A1)),$C$1:$C$8,0))
 
Hi Ali.

Thanks so much! That's an awesome set of formulas and comes very close to achieving what I need, but isn't quite 100% compatible unfortunately. I should've mentioned that although the vast majority of my scores are integers, there are a number that have one decimal place too. As a result, these are clashing with your ".000" text addition in column C.

Any chance of you tweaking accordingly? o_O
 
Wow Ali, it almost seems like you're on permanent duty on this forum!

Definitely most beneficial for me though - your last formula is 100% perfect for my needs, and a thing of some beauty to behold in action.

How do I give you much-deserved trophy points? :):)
 
No need for points, but I think the like button does that. Not on permanent duty, no, but I'm an early riser and am on holiday (vacation) from work at the moment. Glad to have helped! :)
 
You must log in or register to reply here.
Back
Top