software

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(A1lt;gt;quot;quot;,COUNTA($A$1:A1)amp;quot;.quot;,quot;quot;)
B2 =IF(A2lt;gt;quot;quot;,COUNTA($A$1:A2)amp;quot;.quot;,quot;quot;)
B3 =IF(A3lt;gt;quot;quot;,COUNTA($A$1:A3)amp;quot;.quot;,quot;quot;)
B4 =IF(A4lt;gt;quot;quot;,COUNTA($A$1:A4)amp;quot;.quot;,quot;quot;)
B5 =IF(A5lt;gt;quot;quot;,COUNTA($A$1:A5)amp;quot;.quot;,quot;quot;)
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

quot;Bob Stearnsquot; gt; wrote in message
. ..
gt;I would like to automatically number the occurrences found in column A in
gt;column B. For example:
gt;
gt; A1: One B1: 1 (First occurrence of One)
gt; A2: One B2: 2 (Second occurrence of One)
gt; A3: One B3: 3 (Third occurrence of One)
gt; A4: Two B4: 1 (First occurrence of Two)
gt; A5: Two B5: 2 (Second occurrence of Two)
gt;
gt; I am partially there using:
gt; B1 =IF(A1lt;gt;quot;quot;,COUNTA($A$1:A1)amp;quot;.quot;,quot;quot;)
gt; B2 =IF(A2lt;gt;quot;quot;,COUNTA($A$1:A2)amp;quot;.quot;,quot;quot;)
gt; B3 =IF(A3lt;gt;quot;quot;,COUNTA($A$1:A3)amp;quot;.quot;,quot;quot;)
gt; B4 =IF(A4lt;gt;quot;quot;,COUNTA($A$1:A4)amp;quot;.quot;,quot;quot;)
gt; B5 =IF(A5lt;gt;quot;quot;,COUNTA($A$1:A5)amp;quot;.quot;,quot;quot;)
gt; but, I need to somehow add the conditional if A changes B restarts
gt; numbering at 1. The file I'm going to copy into column A contains about
gt; 3,000 lines/rows.
gt;
gt; Thanks for your help...
gt;
gt;
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
lt;ggt;)

With source data in A1 down,
Put in B1: =IF(A1=quot;quot;,quot;quot;,COUNTIF($A$1:A1,A1))
Copy down as far as required
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Bob Stearnsquot; gt; wrote in message
. ..
gt; I would like to automatically number the occurrences found in column A in
gt; column B. For example:
gt;
gt; A1: One B1: 1 (First occurrence of One)
gt; A2: One B2: 2 (Second occurrence of One)
gt; A3: One B3: 3 (Third occurrence of One)
gt; A4: Two B4: 1 (First occurrence of Two)
gt; A5: Two B5: 2 (Second occurrence of Two)
gt;
gt; I am partially there using:
gt; B1 =IF(A1lt;gt;quot;quot;,COUNTA($A$1:A1)amp;quot;.quot;,quot;quot;)
gt; B2 =IF(A2lt;gt;quot;quot;,COUNTA($A$1:A2)amp;quot;.quot;,quot;quot;)
gt; B3 =IF(A3lt;gt;quot;quot;,COUNTA($A$1:A3)amp;quot;.quot;,quot;quot;)
gt; B4 =IF(A4lt;gt;quot;quot;,COUNTA($A$1:A4)amp;quot;.quot;,quot;quot;)
gt; B5 =IF(A5lt;gt;quot;quot;,COUNTA($A$1:A5)amp;quot;.quot;,quot;quot;)
gt; but, I need to somehow add the conditional if A changes B restarts
numbering
gt; at 1. The file I'm going to copy into column A contains about 3,000
gt; lines/rows.
gt;
gt; Thanks for your help...
gt;
gt;
The responses from Biff and Max both work great! Thanks.

---

quot;Bob Stearnsquot; gt; wrote in message
. ..
gt;I would like to automatically number the occurrences found in column A in
gt;column B. For example:
gt;
gt; A1: One B1: 1 (First occurrence of One)
gt; A2: One B2: 2 (Second occurrence of One)
gt; A3: One B3: 3 (Third occurrence of One)
gt; A4: Two B4: 1 (First occurrence of Two)
gt; A5: Two B5: 2 (Second occurrence of Two)
gt;
gt; I am partially there using:
gt; B1 =IF(A1lt;gt;quot;quot;,COUNTA($A$1:A1)amp;quot;.quot;,quot;quot;)
gt; B2 =IF(A2lt;gt;quot;quot;,COUNTA($A$1:A2)amp;quot;.quot;,quot;quot;)
gt; B3 =IF(A3lt;gt;quot;quot;,COUNTA($A$1:A3)amp;quot;.quot;,quot;quot;)
gt; B4 =IF(A4lt;gt;quot;quot;,COUNTA($A$1:A4)amp;quot;.quot;,quot;quot;)
gt; B5 =IF(A5lt;gt;quot;quot;,COUNTA($A$1:A5)amp;quot;.quot;,quot;quot;)
gt; but, I need to somehow add the conditional if A changes B restarts
gt; numbering at 1. The file I'm going to copy into column A contains about
gt; 3,000 lines/rows.
gt;
gt; Thanks for your help...
gt;
gt;
You're welcome !
Thanks for the feedback ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Bob Stearnsquot; gt; wrote in message
...
gt; The responses from Biff and Max both work great! Thanks.