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


"KurtB" wrote:

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.