Index Columns

[doonan36]

If column B2 has Husband, B3 has Wife, B4-B5 has Child then starts over, how can I index each family unit ie: column A2 through A4 = 1 and increases with additional families.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Index[/TD]
[TD]Member
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Husband[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Wife[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Child[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Child[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Husband[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Wife[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Child[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Husband[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Wife[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Husband[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Wife[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Child[/TD]
[/TR]
</tbody>[/TABLE]

 

[MrKowz]

Will "Husband" always denote the beginning of a new family? If so, enter 1 in A2 (next to the first Husband) and use the below formula:

Excel 2013/2016
	A	B
1	Index	Member
2	1	Husband
3	1	Wife
4	1	Child
5	1	Child
6	2	Husband
7	2	Wife
8	2	Child
9	3	Husband
10	3	Wife
11	4	Husband
12	4	Wife
13	4	Child
Sheet3

Cell Formulas
Range	Formula
A3	=IF(B3="Husband",A2+1,A2)

 

[doonan36]

If column B2 has Husband, B3 has Wife, B4-B5 has Child then starts over, how can I index each family unit ie: column A2 through A4 = 1 and increases with additional families.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Index[/TD]
[TD]Member[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Husband[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Wife[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Child[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Child[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Husband[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Wife[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Child[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Husband[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Wife[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Husband[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Wife[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Child[/TD]
[/TR]
</tbody>[/TABLE]

This worked flawlessly, Thank you.

I modified it for blank cells.

=IF(ISBLANK(B3),"",IF(B3="Husband",A2+1,A2))

Cell A2=0


Do you have any suggestions on how to vb this?

Mark

 

[MrKowz]

Give this code a shot:

Public Sub familyindex()
Dim i           As Long, _
    LR          As Long, _
    lngIndex    As Long

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

LR = Range("B" & Rows.Count).End(xlUp).Row

For i = 2 To LR
    Select Case Range("B" & i).Value
        Case "Husband"
            lngIndex = lngIndex + 1
            Range("A" & i).Value = lngIndex
        Case ""
            'Do Nothing
        Case Else
            Range("A" & i).Value = lngIndex
    End Select
Next i
        
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
        
End Sub

It generated the below results (I added some blank rows in there to demonstrate its capability to pass over them):

Excel 2013/2016
	A	B
1	Index	Member
2	1	Husband
3	1	Wife
4	1	Child
5	1	Child
6
7	2	Husband
8	2	Wife
9
10
11
12	2	Child
13	3	Husband
14	3	Wife
15
16
17
18	4	Husband
19	4	Wife
20	4	Child
Sheet4

 

[doonan36]

I will give this a try and let you know how it works in my project... Thank you very much for your assistance on this.

Mark

 

[doonan36]

WOW! I am learning so much from you guys!! Thank you for all of your help on this!

Mark