How to Count Duplicate Value with excel vba

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    MOD EDIT: I'm adding the crosspost here: mrexcel.com/board/threads/exce…-duplicate-value.1156838/



    Column A have many ID, there are duplicate value such as cell(A2) ID 300502489400, cell(A3) ID 300502520900, cell(A4) ID 300502520900, cell(A5) ID 300502520900, cell(A6) ID 300502523900, cell(A7) ID 300502520900,

    i need to count number of duplicate and report in Column B result such as cell(B2) =1, cell(B3) =4, cell(B4) =4, cell(B5) =4, cell(B6) =1, cell(B7) =1


    column B is the answer. i need to use vba to compare value in cell A2 will all value in column A and count number of value the same as cell A2 and report in cell B2. next compare value in cell A3 with all value in column A and count number of value the same as cell A3 and report in cell B3 something like this.

  • Hello,


    Welcome to the Forum :)


    Much better than an image ... you should attach a sample file ;)


    Why do you need a VBA solution ... when a simple pivot table will produce your report within a couple of seconds ...

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

  • Column A have many ID, there are duplicate value such as cell(A2) ID 300502489400, cell(A3) ID 300502520900, cell(A4) ID 300502520900, cell(A5) ID 300502520900, cell(A6) ID 300502523900, cell(A7) ID 300502520900,

    i need to count number of duplicate and report in Column B result such as cell(B2) =1, cell(B3) =4, cell(B4) =4, cell(B5) =4, cell(B6) =1, cell(B7) =1


    column B is the answer. i need to use vba to compare value in cell A2 will all value in column A and count number of value the same as cell A2 and report in cell B2. next compare value in cell A3 with all value in column A and count number of value the same as cell A3 and report in cell B3 something like this.

    Book1.xlsm