How to add a index number to a helper column

[JeffGrant]

Hi All, I am having a real brain fade today.

My aim is to keep visible the first occurrence of a record, then hide the rest.

Bu I need to create a helper column such as this.

Book1
	A	B
1	Name	Index
2	Jeff	1
3	Jeff
4	Jeff
5	Tom	1
6	Tom
7	Tom
8	Tim	1
9	Tim
10	Noel	1
11	Fred	1
12	Fred
13	Joe	1
14	Joe
15	Joe
Sheet1

Ordinarily I would research until I found a For Next Loop somewhere on the big www, but I have over 10,000 records, so I am hoping there is a better and more efficient way.

I am not familiar enough with PQ.

If a loop is the preferred way, can someone be kind enough to write one. I am sure what you folk can do in a matter of minutes will take me considerable time to find.

Much appreciated.

J.

 

[Fluff]

Why not just use a formula

=IF(COUNTIF(A$2:A2,A2)=1,1,"")

 

[Snakehips]

@JeffGrant Are you needing vba and the helper index?

If you apply advanced filter to the Name column and select Unique rcords only then it will hide all bar the first instance.

 

[alansidman]

Option Explicit

Sub CountNames()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("B1") = "Index"
    Range("B2") = 1
    For i = 3 To lr
        If Range("A" & i) = Range("A" & i - 1) Then
            Range("B" & i) = ""
        Else: Range("B" & i) = 1
        End If
    Next i
    MsgBox "completed"
End Sub
Option Explicit

 

[Fluff]

If you do need a macro, another option is

Sub JeffGrant()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long
   
   Ary = Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary), 1 To 1)
   Nary(1, 1) = "Index"
   With CreateObject("scripting.dictionary")
      For r = 2 To UBound(Ary)
         If Not .exists(Ary(r, 1)) Then
            .Add Ary(r, 1), Nothing
            Nary(r, 1) = 1
         End If
      Next r
   End With
   Range("B1").Resize(UBound(Nary)).Value = Nary
End Sub

 

[JeffGrant]

@JeffGrant Are you needing vba and the helper index?

If you apply advanced filter to the Name column and select Unique rcords only then it will hide all bar the first instance.

Hi Snakehips,

Didn't realise I could that.

So I have selected the entire range
added the filter
clicked on advanced to select Unique records
entered the criteria range

and all it does is remove the filters and leave the block high lighted.

Any ideas why it would be doing that? What did I do wrong?

 

[JeffGrant]

Why not just use a formula

=IF(COUNTIF(A$2:A2,A2)=1,1,"")

Thanks Fluff, this was an easy solution.

A combination of this plus Snakehips did the trick.

I appreciated every body rapid assistance.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[JeffGrant]

Option Explicit

Sub CountNames()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("B1") = "Index"
    Range("B2") = 1
    For i = 3 To lr
        If Range("A" & i) = Range("A" & i - 1) Then
            Range("B" & i) = ""
        Else: Range("B" & i) = 1
        End If
    Next i
    MsgBox "completed"
End Sub
Option Explicit

Thanks Alan. I have another spot where I need to actually add lines where the number of records is less than 3, so that all names have three records. I need this because I need to fill data for a linear regression that needs to get done. I will adapt this code for that. Thanks again.

 

[Snakehips]

Hi Snakehips,

Didn't realise I could that.

So I have selected the entire range
added the filter
clicked on advanced to select Unique records
entered the criteria range

and all it does is remove the filters and leave the block high lighted.

Any ideas why it would be doing that? What did I do wrong?

Just edit the range to be the name column only.