Home

Unique Zip codes

%3CLINGO-SUB%20id%3D%22lingo-sub-885719%22%20slang%3D%22en-US%22%3EUnique%20Zip%20codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-885719%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20want%20a%20count%20on%20how%20many%20people%20live%20in%20each%20zip%20code%20in%20the%20attached%20file.%20It%20has%20one%20person%20per%20row.%20They%20just%20want%20a%20total%20for%20each%20zip%20code.%20BUT%20the%20zip%20codes%20are%20XXXXX-YYYY...Can%20I%20count%20the%20unique%20codes%20for%20just%20the%20first%205%20digits%20(not%20the%20YYYY).%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-885719%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-885764%22%20slang%3D%22en-US%22%3ERe%3A%20Unique%20Zip%20codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-885764%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417986%22%20target%3D%22_blank%22%3E%40BoTurner77%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20create%20a%20helper%20column%20to%20extract%20the%205%20digits%20from%20the%20zip%20codes%20and%20then%20insert%20a%20Pivot%20Table%20to%20get%20the%20count%20for%20each%205%20digit%20zip%20code%20as%20shown%20in%20the%20attached%20file.%20Filter%20the%20Pivot%20Table%20to%20exclude%20the%20blank%20empty%20zip%20code%20and%20other%20not%20desired%20zip%20codes%20and%20then%20sort%20it%20in%20descending%20order.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20to%20get%20the%205%20digit%20Zip%20code%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DLEFT(%5B%40Zip%5D%2C5)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
BoTurner77
Occasional Visitor

We want a count on how many people live in each zip code in the attached file. It has one person per row. They just want a total for each zip code. BUT the zip codes are XXXXX-YYYY...Can I count the unique codes for just the first 5 digits (not the YYYY). 

1 Reply

@BoTurner77 

You may create a helper column to extract the 5 digits from the zip codes and then insert a Pivot Table to get the count for each 5 digit zip code as shown in the attached file. Filter the Pivot Table to exclude the blank empty zip code and other not desired zip codes and then sort it in descending order.

 

Formula to get the 5 digit Zip code:

=LEFT([@Zip],5)

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies