Auto-Numbering Occurrences Question
 

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...

Auto-Numbering Occurrences Question
 

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...

Auto-Numbering Occurrences Question
 

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...

Auto-Numbering Occurrences Question
 

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...

Auto-Numbering Occurrences Question
 

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