# assign occurence number

O

#### omega

Hi,
How do you assign an occurrence number in a cell?
Letâ€™s say Column B (B1:B20).
Restriction: 1 is the only valid data to be entered in Column B.

Usage possibilities:
If the user enters â€œ1â€ at B3, an occurrence number of â€œ1â€ appears at C3.
If the user enters â€œ1â€ at B12, an occurrence number of â€œ2â€ appears at C12.
If the user enters â€œ1â€ at B1, an occurrence number of â€œ3â€ appears at C1.
If the user enters â€œ1â€ at B20, an occurrence number of â€œ4â€ appears at C20.
And so onâ€¦

Objective: Chronological cell usage indicator.
The occurrence number will be used by another sheet.

Iâ€™m still new to excel so please indicate extra details needed for beginners
like me.

Thank you,

Omega

O

#### Otto Moehrbach

To do chronological counting like you want, you will need VBA. Right-click
your sheet tab, select View Code, and paste this macro into that module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("B1:B20")) Is Nothing Then
Target.Offset(, 1) = _
Application.CountIf(Range("B1:B20"), 1)
End If
End Sub

O

#### omega

Otto,

It works well. Iâ€™m amazed! Although, there is another possibility that the
user will do that I was not able to mention in my first letter. Given the
possibilities mentioned there, the user entered the required data, â€œ1â€ at B3,
B12, B1 and B20. Now the user decided to delete data in B12, then enters â€œ1â€
in B13, this will result to a duplicate of occurrence # 4. There will be 2
occurrences of â€œ4â€ now. These are B20 and B13. Is there a way to eliminate

Please include an optional line just in case I will decide to make the
occurrences appear in Sheet2.

Thank you very much for your help.

Omega

=======================

O

#### Otto Moehrbach

Think about it for a minute. What do you want Excel to put into that cell
if not another "4"? Write down the logic tree that Excel might be able to
use to do what you want. HTH Otto