I would like to automatically number the occurrences found in column A in
column B. For example:

A1: One B1: 1 (First occurrence of One)
A2: One B2: 2 (Second occurrence of One)
A3: One B3: 3 (Third occurrence of One)
A4: Two B4: 1 (First occurrence of Two)
A5: Two B5: 2 (Second occurrence of Two)

I am partially there using:
B1 =IF(A1<"",COUNTA($A$1:A1)&".","")
B2 =IF(A2<"",COUNTA($A$1:A2)&".","")
B3 =IF(A3<"",COUNTA($A$1:A3)&".","")
B4 =IF(A4<"",COUNTA($A$1:A4)&".","")
B5 =IF(A5<"",COUNTA($A$1:A5)&".","")
but, I need to somehow add the conditional if A changes B restarts numbering
at 1. The file I'm going to copy into column A contains about 3,000
lines/rows.

Thanks for your help...

Hi!

Try this:

Enter 1 in B1.

Enter this formula in B2 and copy down as needed:

=IF(A2=A1,B1+1,1)

Biff

"Bob Stearns" wrote in message
. ..
I would like to automatically number the occurrences found in column A in
column B. For example:

A1: One B1: 1 (First occurrence of One)
A2: One B2: 2 (Second occurrence of One)
A3: One B3: 3 (Third occurrence of One)
A4: Two B4: 1 (First occurrence of Two)
A5: Two B5: 2 (Second occurrence of Two)

I am partially there using:
B1 =IF(A1<"",COUNTA($A$1:A1)&".","")
B2 =IF(A2<"",COUNTA($A$1:A2)&".","")
B3 =IF(A3<"",COUNTA($A$1:A3)&".","")
B4 =IF(A4<"",COUNTA($A$1:A4)&".","")
B5 =IF(A5<"",COUNTA($A$1:A5)&".","")
but, I need to somehow add the conditional if A changes B restarts
numbering at 1. The file I'm going to copy into column A contains about
3,000 lines/rows.

Thanks for your help...

Another play to try, which can be copied straight down from the starting
cell, and even beyond existing data in the col (in readiness for future data
<g)

With source data in A1 down,
Put in B1: =IF(A1="","",COUNTIF($A$1:A1,A1))
Copy down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bob Stearns" wrote in message
. ..
I would like to automatically number the occurrences found in column A in
column B. For example:

A1: One B1: 1 (First occurrence of One)
A2: One B2: 2 (Second occurrence of One)
A3: One B3: 3 (Third occurrence of One)
A4: Two B4: 1 (First occurrence of Two)
A5: Two B5: 2 (Second occurrence of Two)

I am partially there using:
B1 =IF(A1<"",COUNTA($A$1:A1)&".","")
B2 =IF(A2<"",COUNTA($A$1:A2)&".","")
B3 =IF(A3<"",COUNTA($A$1:A3)&".","")
B4 =IF(A4<"",COUNTA($A$1:A4)&".","")
B5 =IF(A5<"",COUNTA($A$1:A5)&".","")
but, I need to somehow add the conditional if A changes B restarts
numbering
at 1. The file I'm going to copy into column A contains about 3,000
lines/rows.

Thanks for your help...

The responses from Biff and Max both work great! Thanks.

---

"Bob Stearns" wrote in message
. ..
I would like to automatically number the occurrences found in column A in
column B. For example:

A1: One B1: 1 (First occurrence of One)
A2: One B2: 2 (Second occurrence of One)
A3: One B3: 3 (Third occurrence of One)
A4: Two B4: 1 (First occurrence of Two)
A5: Two B5: 2 (Second occurrence of Two)

I am partially there using:
B1 =IF(A1<"",COUNTA($A$1:A1)&".","")
B2 =IF(A2<"",COUNTA($A$1:A2)&".","")
B3 =IF(A3<"",COUNTA($A$1:A3)&".","")
B4 =IF(A4<"",COUNTA($A$1:A4)&".","")
B5 =IF(A5<"",COUNTA($A$1:A5)&".","")
but, I need to somehow add the conditional if A changes B restarts
numbering at 1. The file I'm going to copy into column A contains about
3,000 lines/rows.

Thanks for your help...

You're welcome !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bob Stearns" wrote in message
...
The responses from Biff and Max both work great! Thanks.

Hi all (is anybody out there? I see this post is a few years old!)

This has been a really helpful formula for me, but I know need to number ocurrences matching multiple (two) criteria:

A1=Cat B1=Black C1=1
A2=Cat B2=Black C2=2
A3=Cat B3=White C3=1
A4=Dog B4=Black C4=1
A5=Dog B5=Black C5=2

Anyone have a solution for this? I've tried playing with If statement below but this is just beyond my skills!

Many thanks