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


    Hello,


    For ultra fast ... why not inserting a Pivot Table ...??? :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: VBA code to count duplicates FAST


    You could try this. This uses a dictionary


  • Re: VBA code to count duplicates FAST


    Quote from bryce;787014

    You could try this. This uses a dictionary



    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


    Quote from bryce;787014

    You could try this. This uses a dictionary




    How I can use it to be able count in range of cells A1:K20 ???

  • Re: VBA code to count duplicates FAST


    Hello Jan,


    The Dictionary object is very handy for Duplicates ...


    What is exactly your count problem ...?


    Can you attach a sample workbook ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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


  • Re: VBA code to count duplicates FAST


    Hello,


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

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: VBA code to count duplicates FAST


    Quote

    How I can use it to be able count in range of cells A1:K20 ???


    Hi Jan,


    If you just want to know the number of unique entries in A1:K20 without any other criteria, based on the SUMPRODUCT from thread 3 from this post you can use this worksheet formula...


    =SUMPRODUCT(($A$1:$K$20<>"")/COUNTIF($A$1:$K$20,$A$1:$K$20&""))


    ...which can be used in code like so:


    Code
    1. Option Explicit
    2. Sub Macro2()
    3. MsgBox Evaluate("SUMPRODUCT((A1:K20<>"""")/COUNTIF(A1:K20,A1:K20&""""))")
    4. End Sub


    HTH


    Robert

  • Hi everyone,
    I too has a similar request, but a little different, in my case i want to take out the Unique values from the list of duplicates jobs# & copy the unique jobs# into another column, & then pull out the respective status into another column, as shown in the attached Excel ,"Example" sheet


    Note: Unique status will be updated only if all the duplicate jobs# has the respective status , else it will be blank


    i would appreciate if you could provide me some help on this
    pls. find the excel file in the attachment!


    Thanking you in anticipation
    Abrar

  • Hello,


    This thread is over a year old ...:wink:


    Would recommend you create you own thread ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)