Excel - A Macro for counting duplicates in row

Ask a question



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.

Excel - The ISBLANK function
How To Convert Numbers to Words in Excel