Formula to count reoccurrences in a data set

Question

I have a data set of dates and locations that serves as a record of the location for each day of the year.

eg

Date	Location
01/01/23	London
02/01/23	London
03/01/23	Bristol
04/01/23	London
05/01/23	Manchester
06/01/23	London
07/01/23	London
08/01/23	Bristol

I would like help with a formula that populates a third column that counts independent visits to each place.

eg

Date	Location	Location#
01/01/23	London	London1
02/01/23	London	London1
03/01/23	Bristol	Bristol1
04/01/23	London	London2
05/01/23	Manchester	Manchester1
06/01/23	London	London3
07/01/23	London	London3
08/01/23	Bristol	Bristol2

How could I write this with a formula (happy to create additional column is it not possible to write into one formula)?

I have no idea where to start with this. Ideally, I want to avoid using an array.

Answer 1

Here's one solution (Sheets).

=ARRAYFORMULA(
   LET(l,B2:B,
       r,ROW(l),
       u,IF(l=OFFSET(l,-1,,ROWS(l)),,l),
       IF(l="",,l&SCAN(,COUNTIFS(u,u,r,"<="&r),LAMBDA(a,v,IF(v,v,a))))))