Sorting A large list into smaller lists on different sheets

mkeohan

New Member
Joined
Jun 14, 2018
Messages
13
Hello Everyone,

I currently get a list of addresses from a lead generation company daily. I want to be able to take that list and segment it into smaller lists by zip codes that are in each of my companies territories. This way I can make Mail Merge documents from the smaller lists so they correspond with the correct office. The way I have it set up currently is the main data set is on the first sheet marked Full List. The second sheet is the first list but with customers with fuel type Gas removed. The third sheet is a break down of the territories for each office with both the town name and zip code. The other sheets are where I want the matched entries to go from the second sheet so I can make the Mail Merge Documents. If you have any questions please let me know and I will do my best to clarify. Thank you in advance I've been banging my head against this problem for two days and I'm no closer to a solution.

In the dropbox link down below I have the file.


https://www.dropbox.com/s/nuqscmvu1fvdla6/Zip Leads Back Up System.xlsx?dl=0
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi & welcome to MrExcel
How about
Code:
Sub Splitdata()
   Dim Ary() As String
   Dim i As Long, j As Long
   Dim Mws As Worksheet
   Dim Tws As Worksheet
   
   Set Mws = Sheets("Oil & Unknown")
   Set Tws = Sheets("Territory")
   For i = 2 To 20 Step 3
      ReDim Ary(0 To Tws.Cells(Rows.Count, i).End(xlUp).row - 2)
      For j = 3 To UBound(Ary) + 2
         Ary(j - 2) = "0" & Tws.Cells(j, i)
      Next j
      Mws.Range("A1:X1").AutoFilter 12, Ary, xlFilterValues
      Mws.AutoFilter.Range.Copy Sheets(Split(Tws.Cells(1, i - 1))(0)).Range("A1")
   Next i
End Sub
 
Upvote 0
Thank you it's nice to be here. Thank you for your quick response. How do I apply this code?
 
Upvote 0
Have a look here to see how to install & run macros.
 
Upvote 0
Last question: will this work if I paste in a new data set or will I have to adjust the Macro in any way?
 
Upvote 0
It will still work, but it will overwrite the existing data in the sheets.
However if there is more data in the Winchester sheet, than new data some of the old data will remain.
 
Upvote 0
Hi
Create a new sheet called Lookups.
Copy all of the Zip codes you want into column A and in column B type the name of the Territory
Code:
[TABLE="width: 142"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Zip[/TD]
[TD]Territory[/TD]
[/TR]
[TR]
[TD]03087[/TD]
[TD]Winchester[/TD]
[/TR]
[TR]
[TD]03079[/TD]
[TD]Winchester[/TD]
[/TR]
[TR]
[TD]03053[/TD]
[TD]Winchester[/TD]
[/TR]
[TR]
[TD]01701[/TD]
[TD]Winchester[/TD]
[/TR]
[TR]
[TD]01701[/TD]
[TD]Winchester[/TD]
[/TR]
[TR]
[TD]01702[/TD]
[TD]Winchester[/TD]
[/TR]
[TR]
[TD]01703[/TD]
[TD]Winchester[/TD]
[/TR]
</tbody>[/TABLE]
Ensure that the Zip field is Text and has the leading zeros.

Add one extra column to your amended data called Territory and enter the formula
=VLOOKUP(L2,Lookups!A:B,2,0)

Now Create a Pivot Table from your source data, and add all of the columns to the Row area of the PT, apart from Territory, which you must add to the Filter area.

Select the territory you want, and you will have a list of the Customers for that area.

You can delete all of the other sheets you have for the territories in your file, because the PT can generate these for you.
With your cursor within the PT, click on the Analyse tab.
Then in the first section, Click on Options and choose Show Report Filter Pages.
Select the list of all of the territories you want, and Excel will generate a PT report with a sheet for each territory.
 
Upvote 0
Hi Fluff

Very nice piece of code.
Had I seen your response, I wouldn't have bothered with my Pivot Table solution.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top