Making a league table - duplication problems

  • Thread starter Thread starter Col
  • Start date Start date
C

Col

Hi all,

OK, I'm trying to make a league table of our branches within the company
based on various criteria then put them in order of points 'scored', I wish
to do this automatically.

Am using the LARGE function along with INDEX/MATCH, which is fine however if
two branches have the same score then in the resulting table both entries
are given the same name.

So if I have the following results in a worksheet;

Branch A 60pts
Branch B 80pts
Branch C 60pts
Branch D 20pts
Branch E 60pts

After the formulas have done their stuff I get something like this;

Branch B 80pts
Branch A 60pts
Branch A 60pts
Branch A 60pts
Branch D 20pts

How can I get Excel to look at adjacent cells and if it finds a match then
looks for another branch with the same value and use that one instead?

A pointer to any new functions may suffice as I'm happy to give them a go
myself.

Many thanks for any help,

Colin.
 
Col,

Add a unique rank into a helper column, and do your lookup based upon that

=RANK(B1,$B$1:$B$9)+COUNTIF($B$1:B1,B1)-1

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top