Excel Help for Counting Duplicate Zip Codes

Question

I'm in serious need for some help. I'm working on an Excel sheet which showcases zip codes for 3 different categories. Each category has zip codes in one column, and then in the next I used COUNTIF to show how many times each zip code is repeated.

However, I have over 100k+ amounts of zip codes. I'm trying to find a way to consolidate this. What I want to do is be able to just have one zip code appear each time in the first column, and then in the next tell me how many times that zip code was repeated, without having the zip codes themselves duplicated.

So, instead of it looking like: 60618 -- 3

                                            60618 -- 3

                                            60618 -- 3

                                            45278 -- 2

                                            45278 -- 2

                                            98067 -- 1

I need it to look like: 60618 -- 3

                                             45278 -- 2

                                             98067 -- 1

Here is an image of my current document:

Answer 1

Hi HailyBuckman, hope you're doing well. I’m Ian, and I’m happy to help you today.

In this case you need intermediary list which consist the unique data you have.

1. Click the Data Ribbon Menu
2. Select the Advanced Button in the Sort & Filter section
3. Fill in the dialog Box, copying the results to another location and making sure you tick Unique records only
4. After creating a unique list, you can now implement the COUNTIF to count the number of occurrences

Please see sample below.

This is a user-to-user support forum and I am a fellow user.

I hope this helps, but please let me know if you need anything else.

Answer 2

Re: count unique zip codes

The free 'Professional Compare' workbook includes the

'List Unique Items' utility.

It lists and counts the unique items in the selection you make.

A couple of clicks and you are done.

The result is placed in the first set of blank columns to the right of the data...

Download from OneDrive... https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

'---

Nothing Left to Lose