Dumb Question

[wazzulu1]

Hi;

What is the best way to insert a count of the file number in the 3rd column (VBA or Formula)?

I have 22,000 records that I need to group, and figured that if I can count each file number, and place the number in the 3rd column, it would work, just not sure how to do it (Example below)
[TABLE="width: 8"]
<tbody>[TR]
[TD][TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]File Number[/TD]
[TD="class: xl65, width: 64"]TheDate[/TD]
[TD="class: xl65, width: 64"]TheCount[/TD]
[/TR]
[TR]
[TD="class: xl63"]8114344[/TD]
[TD="class: xl64, align: right"]7/30/2018[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8114344[/TD]
[TD="class: xl64, align: right"]4/17/2018[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8114344[/TD]
[TD="class: xl64, align: right"]7/10/2018[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8140397[/TD]
[TD="class: xl64, align: right"]6/27/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8702054[/TD]
[TD="class: xl64, align: right"]10/27/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8702054[/TD]
[TD="class: xl64, align: right"]12/27/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8702054[/TD]
[TD="class: xl64, align: right"]5/26/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8702054[/TD]
[TD="class: xl64, align: right"]2/14/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8702054[/TD]
[TD="class: xl64, align: right"]1/27/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8702054[/TD]
[TD="class: xl64, align: right"]3/28/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8951128[/TD]
[TD="class: xl64, align: right"]3/25/2018[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8939187[/TD]
[TD="class: xl64, align: right"]1/18/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3565194[/TD]
[TD="class: xl64, align: right"]1/11/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3529427[/TD]
[TD="class: xl64, align: right"]9/11/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3510635[/TD]
[TD="class: xl64, align: right"]9/18/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3504885[/TD]
[TD="class: xl64, align: right"]3/7/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3504885[/TD]
[TD="class: xl64, align: right"]2/29/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3835087[/TD]
[TD="class: xl64, align: right"]11/1/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3835087[/TD]
[TD="class: xl64, align: right"]2/27/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3835087[/TD]
[TD="class: xl64, align: right"]10/13/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3835087[/TD]
[TD="class: xl64, align: right"]4/4/2018[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3835087[/TD]
[TD="class: xl64, align: right"]4/12/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3835087[/TD]
[TD="class: xl64, align: right"]6/26/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3830017[/TD]
[TD="class: xl64, align: right"]9/18/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3825126[/TD]
[TD="class: xl64, align: right"]9/1/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3324479[/TD]
[TD="class: xl64, align: right"]11/2/2015[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3302125[/TD]
[TD="class: xl64, align: right"]5/23/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3614666[/TD]
[TD="class: xl64, align: right"]2/9/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3671019[/TD]
[TD="class: xl64, align: right"]1/22/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3671019[/TD]
[TD="class: xl64, align: right"]2/8/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3297700[/TD]
[TD="class: xl64, align: right"]8/22/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[wazzulu1]

Thank You, This works!!!!!!

I have 22,000 records to tag with this, do I have to drag this down manually, or can I use a copy & paste short cut?

 

[alansidman]

Here is an alternate VBA solution

Option Explicit


Sub CountFileNr()
    Dim lr As Long
    Dim i As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("C2") = 1
    For i = 3 To lr
        If Range("A" & i) = Range("A" & i - 1) Then
            Range("C" & i) = Range("C" & i - 1) + 1
        Else: Range("C" & i) = 1
        End If
    Next i
End Sub

 

[Fluff]

Thank You, This works!!!!!!

I have 22,000 records to tag with this, do I have to drag this down manually, or can I use a copy & paste short cut?

As long as you have data in every row of col B, enter the formula in C2 Then put the cursor on the bottom right hand corner of the cell & double click

 

[wazzulu1]

Thanks Alan;

This is great!!!

 

[alansidman]

You are welcome.

 

[wazzulu1]

Thanks for that tip Fluff, never knew that would work!

 

[Fluff]

You're welcome

 

[wazzulu1]

This forum is so valuable, I appreciate everyone's willingness to help others with the knowledge exchange!