Excel - A Macro for counting duplicates in row

July 2017




Issue


I need Macro for counting duplicates in raw, See example below.

State:

PA 
PA 
PA 
MD 
CA 
MT 
NJ 
NJ 


Report Results in next Column

PA 3 
MD 1 
CA 1 
MT 1 
NJ 2 


Can this be done?

Solution

  • Select the column (lets says its A)
  • Go to filter, choose advance filter
    • Select copy to new location
    • Ensure that "List range" is correct
    • Choose Unique value
    • For have it B1 (for example)

This will give you in column B, a listing of unique names present in column A

Now that you have your unique names, it is time for having a count. For that you use a count IF

=COUNTIF(A:A, "=" & B2) 


You can use Excel macro recorder which with give you a macro of these steps.

Thanks to rizvisa1 for this tip.

Related


Published by aakai1056. Latest update on December 1, 2011 at 09:47 AM by aakai1056.
This document, titled "Excel - A Macro for counting duplicates in row," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).