Search code examples
google-sheetsranking

Highest rank within a range - two matching data sets in range


I have a column that looks like this in a spreadsheet for a Fantasy F1 League. I need to find a way to give 5 points to the 1st mention (highest position) of each team car. For example, P1 Mercedes gets 5 points. P3 Mercedes gets 0. P2 Ferrari gets 5 points, P21 Ferrari gets 0 and so on so it looks like this:

mercedes 5
ferrari 5
mercedes 0
red_bull 5
haas 5
toro_rosso 5 
red_bull 0
williams 5
williams 0
mclaren 5
renault 5
sauber 5
manor 5
sauber 0
force_india 5
force_india 0
manor 0
toro_rosso 0
haas 0
mclaren 0
ferrari 0
renault 0

Is there a way of doing this in Google Sheets?

The information will be pulling from an API and the position of each team will change week on week.


Solution

  • In B1 enter 5
    In B2 enter:

    =IF(COUNTIF(A$1:$A2,A2)=1,5,0)
    

    and copy down.

    enter image description here