Sorting A large list into smaller lists on different sheets

[mkeohan]

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

 

[Fluff]

Hi & welcome to MrExcel
How about

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

 

[mkeohan]

Thank you it's nice to be here. Thank you for your quick response. How do I apply this code?

 

[Fluff]

Have a look here to see how to install & run macros.

 

[mkeohan]

It works you're a genius!!!! Thank you so much!!!!

 

[mkeohan]

Last question: will this work if I paste in a new data set or will I have to adjust the Macro in any way?

 

[Fluff]

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.

 

[Roger Govier]

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

[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.

 

[mkeohan]

Perfect thank you again!

 

[Roger Govier]

Hi Fluff

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