[excel] Counting duplicates in Excel

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!

Let excel do the work.

1. Select column
2. Select Data tab
3. Select Subtotal, then "count"
4. 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

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

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

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  |       |        |                   |
  +--------+-------+--------+-------------------+

1. Highlight the column with the name
2. Data > Pivot Table and Pivot Chart
3. Next, Next layout
4. drag the column title to the row section
5. drag it again to the data section
6. Ok > Finish

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.

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.

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.