Sorting Sales by Zip Codes - Need the Subtotal for 3 Seperate Territories

[HermeScherme]

Hi everyone! Looking for help as I am stuck up until this point on a recent project I've been working on. I am trying to sort sales into our different territories by their zip codes. I have all U.S. zip codes that correspond with the said territories laid out into 3 different sheets. What is the best, most automated, way to give me the total of each territory by matching the subtotal & bill to zip code with the 3 other sheets that include a list of all zip codes within their respective territory. The goal is to upload our sales with their bill to zip and run a report each month. Looking for an automated way to upload those two factors each month and for excel to give me a total in each of the 3 terriories. Also, in the future I may expand to include more territories but I assume the same formula will apply. I cannot post a minisheet due to too many cells so I hope a picture can suffice. I will upload or PM a .xlsx or .csv file for those who need it to help me. Thanks!

EDIT: See the attached sample workbook instead of images.

[TMS]

A picture is pretty useless for us to determine a solution. Post a sample workbook. See the yellow banner at the top of the page.

Probably easiest to insert a Pivot Table. Then you can analyse to your heart’s content.

[HermeScherme]

Just edited to add a sample workbook. I've tried a pivot table. The issue is that it gives me the sum for each bill to zip code rather than the total for one of the three terriorties based on its zip code list. Is there a way to do that? We also sell to other zip codes that do not need to be analyized and attached to those three territories.

[JeteMc]

As you are using the 365 version of Excel you could use Power Query and Power Pivot.
1. Convert all ranges to tables
2. Use Power Query to combine the tables from the territory sheets using the following advanced editor code:

Please Login or Register  to view this content.

3. Choose Close and Load to: > select connection only and add to data model
4. Add the table on the Sales with Zip sheet to the data model
5. From the diagram view in the data model connect Zip to Bill to Zip (should automatically form a one to many connection)
6. Produce a pivot table, as modeled on the PT sheet, using the territory field from Query1 (the combined territories table) and Subtotal.
The linked video may also help: https://www.youtube.com/watch?v=jVkWDZ7B-Zs
Let us know if you have any questions.

[HermeScherme]

JeteMc, thank you very much for your time and assistance. This is exactly what we wish to do. However, I couldn't replicate what you created by following your steps and code. I even tried updating the variables (subtotal, bill to zip) on your attached file and errors came up. Is there any further suggestions for me at this point? Again, thanks for your time.

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[JeteMc]

When attempting to Refresh the pivot table I got a message about blanks in the Zip column of the Query1 table so I have added a step to the Advanced Editor code to remove blanks:

Please Login or Register  to view this content.

Hopefully this will solve the issue.
Let us know if you have any questions.