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?
This uses a dictionary
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.
|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|