number lists based on one column
 

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.

number lists based on one column
 

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


"KurtB" skrev:

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.

number lists based on one column
 

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.