CODEWITHSUNDEEP
excel
Mr_Chimp
Mr_Chimp

Reputation: 6907

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!

Upvotes: 32

Views: 211228

Answers (9)

Justyna
Justyna

Reputation: 777

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

Step 1Step 2Step 3

Upvotes: 10

ziad123
ziad123

Reputation: 11

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

Upvotes: 1

Dave
Dave

Reputation: 1

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

Upvotes: -1

datatoo
datatoo

Reputation: 2049

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.

Upvotes: 0

HydTechie
HydTechie

Reputation: 807

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

Upvotes: 2

soho
soho

Reputation: 111

  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

Upvotes: 11

Scott
Scott

Reputation: 231

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.

Upvotes: 23

Gaijinhunter
Gaijinhunter

Reputation: 14685

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.

Upvotes: 3

Corey Ogburn
Corey Ogburn

Reputation: 24717

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

Upvotes: 45

Related Questions