number lists based on one column

[KurtB]

I have a set of data a persons name and a location
Ohio, Fred
Penn, Tim
Illinois, Scott
Ohio, Ted
Penn, tom
Ohio, Ned
Ohio, Ed

Is there a way to number the data based on location:
Ohio, Fred, 1
Penn, Tim, 1
Illinois, Scott, 1
Ohio, Ted, 2
Penn, tom, 2
Ohio, Ned, 3
Ohio, Ed, 4

What I need is to automatically assign a number to the person as I add
them to the list but I need the number assigned by state so there will
be 3 separate tracks for numbers (one for each state). In my list I
will see the each digit three times and they will grow at separate
rates and may shrink if someone from the top or middle is removed.

 

[Guest]

=COUNTIF($A$1:A1,A1)


"KurtB" skrev:

 

[Guest]

Try this formula in an adjacent column:

=SUMPRODUCT(--(LEFT($A$1:A1,FIND(",",A1)-1)=LEFT(A1,FIND(",",A1)-1)))

Copy down as needed.

HTH,
Elkar