VBA code to count duplicates FAST

Hello all,
Need your help with the following:
I have a macro that loops through thousands of cells and creates a long list of item numbers (see attached file, column A).
Once the list is created (as a preparation for the next steps in the code), I need to count how many times each item appears. To be more specific, I need to know the sequential number of the appearance (#1, #2, #3, etc.) (See attached file, column B).
The best way I found to achieve that, is by using the countif function, which the code embeds in the file (later on, the code copy-paste the entire column as values).
The thing is, these formulas take a lot of time to calculate.
My questions is: Is there another way (perhaps an array formula) to get the “counting” added next to each item number (replacing this countif formula), which will work much faster?
All the best,
P.

Re: VBA code to count duplicates FAST

Quote from bryce;787014

You could try this. This uses a dictionary

Code

Sub countThings()
    Dim ws As Worksheet
    Dim lastRow As Long, x As Long
    Dim items As Object
    
    Application.ScreenUpdating = False
    
    Set ws = Sheet1
    
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    Set items = CreateObject("Scripting.Dictionary")
    For x = 1 To lastRow
        If Not items.exists(ws.Range("A" & x).Value) Then
            items.Add ws.Range("A" & x).Value, 1
            ws.Range("C" & x).Value = items(ws.Range("A" & x).Value)
        Else
            items(ws.Range("A" & x).Value) = items(ws.Range("A" & x).Value) + 1
            ws.Range("C" & x).Value = items(ws.Range("A" & x).Value)
        End If
    Next x
    
    
End Sub

Display More

I am speechless :wowee: . This is absolutely mind blowing.
I have been using the countif formula for many years, as a solution to get the sequential number of each duplicate. It always "killed" me because of the time it took to calculate (and re-calculate...).
Your solution is a "life saver".
Thanks you so much !

Re: VBA code to count duplicates FAST

[COLOR="#0000FF"]Sorry. Please ignore this post. I must have done something wrong. Re-tried changing the start and the end point and everything works as I thought it should...
[/COLOR]
Hi there,
Going back to the brilliant code below, can anyone help me with the following:?
This code loops through an entire column, but the problem is - it also counts the header cell of the column and places the results in the relevant column while stepping over the header name of that column.
Is there a way to tell the code to start the count (and the results) at a different row? Instead of starting at row 1 - start at row 2?
I thought the "x" in the for-loop was supposed to handle this, but when I changed the line to "For x = 2 To lastRow" - the result was the same as x=1 (meaning it started at row 1)
In fact, when I try to limit the count to a number of specific rows (say between row 2 and 10) and I play with the value of "lastRow", the resulting counted rows is less that the number of actual data rows (meaning if lastrow =10, I only see 7 results instead of 10).
As I don't really understand how the dictionary method work (and used the code as a "black box") - the whole thing is very strange and got me quite lost.

Any thoughts, anyone?
TIA

Quote from bryce;787014

You could try this. This uses a dictionary

Code

Sub countThings()
    Dim ws As Worksheet
    Dim lastRow As Long, x As Long
    Dim items As Object
    
    Application.ScreenUpdating = False
    
    Set ws = Sheet1
    
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    Set items = CreateObject("Scripting.Dictionary")
    For x = 1 To lastRow
        If Not items.exists(ws.Range("A" & x).Value) Then
            items.Add ws.Range("A" & x).Value, 1
            ws.Range("C" & x).Value = items(ws.Range("A" & x).Value)
        Else
            items(ws.Range("A" & x).Value) = items(ws.Range("A" & x).Value) + 1
            ws.Range("C" & x).Value = items(ws.Range("A" & x).Value)
        End If
    Next x
    
    
End Sub

Display More

Re: VBA code to count duplicates FAST

Quote from Carim;797076

Hello,

Should you still face a problem ... do not hesitate to attach a sample workbook with your next message ...

Thank you !!! Will do...