How to VBA code to count duplicates FAST
Category: [Excel] Demo Available
How to VBA code to count duplicates FAST
Requirement:
The user has 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), the user needs to count how many times each item appears. To be more specific, the user needs to know the sequential number of the appearance (#1, #2, #3, etc.) (See attached file, column B).
The best way the user has 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?
Solution:
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
Obtained from the OzGrid Help Forum.
Solution provided by bryce.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to remove duplicates from dropdown list combobox |
| How to create a macro to move duplicates |
| How to copy a sheet and rename from a list, ignore duplicates |
| How to use IndexMatch formula that ignores duplicates |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.