Counting duplicates in Excel

Question

I have a list of postcodes that includes duplicates  I would like to find out how many instances of each postcode there are    For example I would like this   GL15 GL15 GL15 GL16 GL17 GL17 GL17      to become this   GL15 3 GL15 3 GL15 3 GL16 1 GL17 2 GL17 2      or ideally this   GL15 3 GL16 1 GL17 3   Thanks

User · Answer

Step 1  Select top cell of the data   Step 2   Select Data   Sort   Step 3   Select Data  Subtotal  Step 4   Change use function to  count  and click OK   Step 5   Collapse to 2

User · Answer

Let excel do the work    Select column Select Data tab Select Subtotal  then  count  DONE   Adds it up for you and puts total  Trinidad Count  99     Trinidad Colorado     Trinidad Colorado     Trinidad Colorado     Trinidad Colorado     Trinidad Colorado     Trinidad Colorado Trinidad Colorado Count 6     Trinidad      Trinidad  Trinidad  Count 2     winnemucca     Winnemucca     Winnemucca     Winnemucca     Winnemucca     winnemucca     Winnemucca     Winnemucca     Winnemucca     winnemucca     Winnemucca     Winnemucca     Winnemucca     Winnemucca winnemucca Count    14

User · Answer

I don t know if it s entirely possible to do your ideal pattern  But I found a way to do your first way  CountIF   ------- -------------------      A             B            ------- -------------------    GL15     COUNTIF A A  A1     ------- -------------------    GL15     COUNTIF A A  A2     ------- -------------------    GL15     COUNTIF A A  A3     ------- -------------------    GL16     COUNTIF A A  A4     ------- -------------------    GL17     COUNTIF A A  A5     ------- -------------------    GL17     COUNTIF A A  A6     ------- -------------------

User · Answer

If you perhaps also want to eliminate all of the duplicates and keep only a single one of each  Change the formula  COUNTIF A A A2  to  COUNIF  A 2 A2 A2  and drag the formula down  Then autofilter for anything greater than 1 and you can delete them

User · Answer

You can achieve your result in two steps  First  create a list of unique entries using Advanced Filter    from the pull down Filter menu  To do so  you have to add a name of the column to be sorted out  It is necessary  otherwise Excel will treat first row as a name rather than an entry  Highlight column that you want to filter  A in the example below   click the filter icon and chose  Advanced Filter      That will bring up a window where you can select an option to  Copy to another location   Choose that one  as you will need your original list to do counts  in my example I will choose C C   Also  select  Unique record only   That will give you a list of unique entries  Then you can count their frequencies using  COUNTIF   command  See screedshots for details   Hope this helps      -------- ------- -------- -------------------        A        B       C              D              -------- ------- -------- -------------------  1   ToSort           ToSort                          -------- ------- -------- -------------------  2    GL15            GL15      COUNTIF A A  C2       -------- ------- -------- -------------------  3    GL15            GL16      COUNTIF A A  C3       -------- ------- -------- -------------------  4    GL15            GL17      COUNTIF A A  C4       -------- ------- -------- -------------------  5    GL16                                            -------- ------- -------- -------------------  6    GL17                                            -------- ------- -------- -------------------  7    GL17                                            -------- ------- -------- -------------------

User · Answer

If you are not looking for Excel formula  Its easy from the Menu   Data Menu --  Remove Duplicates would alert  if there are no duplicates  Also  if you see the count and reduced after removing duplicates

User · Answer

Say A A contains the post codes  you could add a B column and put a 1 in each cell  In C1  put  SUMIF A A  A1  B B  and Drag it down your sheet  That would give you the first desired result listed in your question   EDIT   As Corey pointed out  you can just use COUNTIF A A  A1   As I mentioned in the comments you can copy paste special the row with formulas to hard code the counts  the select column A and click remove duplicates  entire row  to get your ideal result

User · Answer

This can be done using pivot tables  See this youtube video for a walkthrough  Quickly Count Duplicates in Excel List With Pivot Table      To count the number of times each item is duplicated in an   Excel list  you can use a pivot table  instead of manually creating a list with formulas

User · Answer

Highlight the column with the name Data   Pivot Table and Pivot Chart Next  Next layout drag the column title to the row section  drag it again to the data section Ok   Finish

[excel] Counting duplicates in Excel

Examples related to excel