Repeated numbering within a column

**cmaskew** · 09-10-2010, 08:11 PM

I need a formula to return a text string along with a number and once the text string doesn't appear and then reappears in the column have the numbering start over again.

For example in column A I have data:
Employee
Spouse
Child
Child
Child
Employee
Child
Employee
Child
Child

I need the data to appear:
Employee
Spouse
Child1
Child2
Child3
Employee
Child1
Employee
Child1
Child2

I have over 7,000 lines of relationship codes and need to number each child within a family.

**Cutter** · 09-10-2010, 08:53 PM

One way, involving 2 helper columns:

With your sample shown in Column A starting in A1
Place this formula in B2:

=IF(AND(A2="Child",A1<>"Child"),1,IF(AND(A2="Child",A1="Child"),B1+1,""))

Place this 2nd formula in C1:

=A1&B1

Drag this 2nd formula down to C2
Now select Range B2:C2 and drag both formulas down as far as needed

When done you can copy the C column and Paste Special - Values over the A column and then delete the B and C columns

**stanleydgromjr** · 09-10-2010, 10:46 PM

cmaskew,

Welcome to the Excel Forum.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Option Explicit
Sub NbrChild()
' stanleydgromjr, 09/10/2010, EF745281
Dim c As Range, a As Long
Application.ScreenUpdating = False
a = 0
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If Trim(c) <> "Child" Then
    a = 0
  Else
    a = a + 1
    c = c & a
  End If
Next c
Application.ScreenUpdating = True
End Sub

Then run the "NbrChild" macro.