# Macro for counting duplicates in raw

Solved/Closed
smstha
Posts
2
Registration date
Monday June 7, 2010
Status
Member
Last seen
June 8, 2010
- Jun 7, 2010 at 02:21 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Nov 30, 2011 at 05:36 PM
Hello,
Ok,
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?
for this I used to use formula
Assume data starting from A:
=IF(COUNTIF(\$A\$2:A2,A2)=1,COUNTIF(A:A,A2),"")
Column 'A': State
PA
PA
PA
MD
CA
MT
NJ
NJ
In Column'B': Report Results
3

1
1
1
2
respectively, for this I wanna do by Macro, Can this be done?

Thanks and regards
smstha
chennai

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jun 7, 2010 at 05:39 AM
You are pretty close to what you want. You can do this

1. Select the column (lets says its A)
2. Go to filter, choose advance filter
2a. select copy to new location
2b. Ensure that "List range" is correct
2c. choose Unique value
2d. 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
smstha
Posts
2
Registration date
Monday June 7, 2010
Status
Member
Last seen
June 8, 2010

Jun 8, 2010 at 05:45 AM
Thank you so much
smstha
chennai
Hi,

Thanks a lot for the answer. Is there a way to use a formula to get the uniques instead of advanced filter or pivot table so that it gets generated automatically instead of the extra step?
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Nov 30, 2011 at 05:36 PM
@sam: based on example data in the original query, the states were not unique. If you have unique list of values then you can simple use the count if. If you do not have the unique list, then one has to generate it. You can do it manually ( as in the solution https://ccm.net/forum/affich-387508-macro-for-counting-duplicates-in-raw#1) or you have this done via macro too.
I am not too sure about formula. Even if it can be done via formula it would need to be an array formula and formula makes excel respond slowly as thing get changed.