0
Thanks

A few words of thanks would be greatly appreciated.

Excel - A Macro for counting duplicates in row




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.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
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).

0 Comments