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)

File Number TheDate TheCount 8114344 7/30/2018 8114344 4/17/2018 8114344 7/10/2018 8140397 6/27/2016 8702054 10/27/2016 8702054 12/27/2016 8702054 5/26/2017 8702054 2/14/2017 8702054 1/27/2017 8702054 3/28/2017 8951128 3/25/2018 8939187 1/18/2016 3565194 1/11/2016 3529427 9/11/2017 3510635 9/18/2017 3504885 3/7/2016 3504885 2/29/2016 3835087 11/1/2016 3835087 2/27/2017 3835087 10/13/2017 3835087 4/4/2018 3835087 4/12/2017 3835087 6/26/2017 3830017 9/18/2017 3825126 9/1/2016 3324479 11/2/2015 3302125 5/23/2016 3614666 2/9/2016 3671019 1/22/2016 3671019 2/8/2016 3297700 8/22/2016 <colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[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!