Hi all I couldn't find a solution to this in the other topics so any feedback would be appreciated! Data set NameABACBADC Output I would like NameCountA3B2A3C2B2A3D1C2 To add, I would like to count exact values (A=A and not Aa~A) so I am not sure if (assuming I should use the summarize tool) I should use count or count distinct? Thank you in advance!

COUNTIF - Duplicates within the same column

Hi all

I couldn't find a solution to this in the other topics so any feedback would be appreciated!

Data set

Name

Output I would like

Name	Count
A	3
B	2
A	3
C	2
B	2
A	3
D	1
C	2

To add, I would like to count exact values (A=A and not Aa~A) so I am not sure if (assuming I should use the summarize tool) I should use count or count distinct?

Thank you in advance!

Workflow

Accepted answers

atcodedog05

Hi @h12

Here is how you can do it. You can use summarise tool to make groupby and get count. You can add back the count using find&replace or join tool.

Workflow:

Hope this helps : )

duplicate count.yxmd

All comments

atcodedog05

Hi @h12

Here is how you can do it. You can use summarise tool to make groupby and get count. You can add back the count using find&replace or join tool.

Workflow:

Hope this helps : )

duplicate count.yxmd

h12

Thank you for your prompt response! I updated my post, but if I use count would that cover distinct values? i.e. I would like to count the exact matches in the Name column?

atcodedog05

Hi @h12

Since we are using groupby on name it is only counting the occurrence of each same name.

Count takes in all occurrences whereas count distinct takes in only distinct occurrences. And in this scenario we need count.

Hope this helps : )

h12

Sorry, so it would count each occurrence of the name in its exact form, so if a name is slighlty difference to another (e.g. ABC' and ABC) it would flag it in separate rows?

atcodedog05

Hi @h12

Yes thats correct. And this would be because of groupby applied.

Hope this helps : )

duplicate count.yxmd

h12

Thank you! Just seen your update to your post! Thank you for responding soo quickly!!

h12

Hi @atcodedog05 - just a question, could I use the join tool and join on the 'Name' column rather than using find & replace? Do one have more accuracy than the other?

atcodedog05

Hi @h12

Join tool resets the sort order (changes order in which rows are)

Find and replace maintains the sort order (maintains order in which rows are)

Personal preference is find and replace over join tool in this scenario 🙂

Hope this helps : )

h12

Thank you!!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703