Trying to create a list using zip codes.

[C_Rock]

First of all, I took an Excel class in '05 and no longer have my workbooks and Google hasn't helped me answer this question. I did find this forum and hope someone can help me with this!

I manage a small hotel. I am trying to compile a list of our guests by zip code from the most frequent guest by zip code to the least frequent. The reason is to target my marketing.

My reservation system uses Excel to generate reports, I can delete all the irrelevant columns leaving me with only a list of zip codes. However I have no clue what formula to use or how. I've been messing with it for several hours today and am not having any luck. I have Excel 2016.

Thanks!!!

[leelnich]

Hi C_Rock, welcome to the forum. Are you asking "within each zip code, which guests visit most often"? Or simply "which zip code provides the most guests"?

BTW- A small sample workbook (NOT a picture) helps us to solve your problem quickly and accurately.
Essentially, you're providing some “realistic” data for testing purposes.
- Remove any sensitive or extraneous info.
- Include examples of any variations the code or formula must address.
- Simulate some results to demonstrate what you want.

To attach a workbook:
Click Edit Post (or just start a new reply.)
Click Go Advanced
Scroll down to Manage Attachments and click.
Now just Browse for your file, then click Upload. Simple!

[Logit]

.
Paste this into a Routine Module:

Please Login or Register  to view this content.

Refer to attached workbook.

[MarvinP]

Hi C Rock and welcome to the forum,

I created a thousand (fake) Zip Codes in column A and then did a Pivot Table and Pivot Chart by using "Insert Pivot Table and Chart". I also grouped the Pivot numbers by groups of 100 to see if this makes sense. No formulas or VBA needed to do your problem. See the attached.

You might search for Pivot Tables on YouTube and see how easy they are to create.

Fake Zip Code Pivot Chart Grouped.xlsx

[C_Rock]

Thanks everybody! I just logged on to find your replys. I will apply your instructions later this afternoon! Fingers crossed! Thanks again...

[C_Rock]

EDIT: Sorry for the dupe. I was trying to reply to leelnich.

Thanks! I would like a list of which zip codes provide the most guests. Even if a zip only has 1 guest I still want to know it.

C_Rock

[C_Rock]

First of all, I took an Excel class in '05 and no longer have my workbooks and Google hasn't helped me answer this question. I did find this forum and hope someone can help me with this!

I manage a small hotel. I am trying to compile a list of our guests by zip code from the most frequent guest by zip code to the least frequent. The reason is to target my marketing.

My reservation system uses Excel to generate reports, I can delete all the irrelevant columns leaving me with only a list of zip codes. However I have no clue what formula to use or how. I've been messing with it for several hours today and am not having any luck. I have Excel 2016.

Thanks!!!

Thanks! I would like a list of which zip codes provide the most guests. Even if a zip only has 1 guest I still want to know it.

C_Rock

[leelnich]

Hi all- I used a named range to simplify maintenance. Select you zip codes, then click Define Name on the Formula tab and type in Zips.
Now paste this ARRAY FORMULA* in C2 and copy down:
*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

Please Login or Register  to view this content.

Then paste this in D2 and copy down:

Please Login or Register  to view this content.

Row\Col	A	B	C	D
1	Zips		Zips by Frequency	Count
2	90210		90210	7
3	90212		90212	4
4	90210		90211	2
5	90211
6	90210
7	90211
8	90212
9	90212
10	90210
11	90212
12	90210
13	90210
14	90210