Search code examples
if-statementgoogle-sheetsarray-formulascountingcountif

Assigning a sequential number to each duplicated string in Google Sheets

I'm looking to assign a different value to rows that are duplicated in Google Sheets. Below is an example of my current sheet that is pulled from a query. 

School   Comp #  Name
School 1    3   Student A
School 1    4   Student B
School 2   10   Student C
School 3   15   Student D
School 3   20   Student E
School 4   25   Student F

Desired Outcome

Athlete #   School  Comp #  Name
Athlete 1   School 1    1   Student A
Athlete 2   School 1    2   Student B
Athlete 1   School 2    3   Student C
Athlete 1   School 3    4   Student D
Athlete 1   School 4    5   Student E
Athlete 1   School 5    6   Student F

Since schools may be sending 1 or 2 Athletes, I'm trying to automatically assign students to heats/lanes based on if they are the first or second athlete the school has registered.

Here is copy of the example sheet I'm working with: https://docs.google.com/spreadsheets/d/1X9XVCI0pXIoTmG6rD61wvQXvyhFUlbIEzQztscSh33o/edit?usp=sharing

Solution

  • try:

    =ARRAYFORMULA(IF(A:A="",,"Athlete "&COUNTIFS(A:A, A:A, ROW(A:A), "<="&ROW(A:A))))

    0