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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,928
Office Version
  1. 365
Platform
  1. Windows
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
 

mkeohan

New Member
Joined
Jun 14, 2018
Messages
13
Thank you it's nice to be here. Thank you for your quick response. How do I apply this code?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,928
Office Version
  1. 365
Platform
  1. Windows
Have a look here to see how to install & run macros.
 

mkeohan

New Member
Joined
Jun 14, 2018
Messages
13

ADVERTISEMENT

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

mkeohan

New Member
Joined
Jun 14, 2018
Messages
13
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,928
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jun 19, 2002
Messages
303
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.
 

Roger Govier

Active Member
Joined
Jun 19, 2002
Messages
303
Hi Fluff

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

Watch MrExcel Video

Forum statistics

Threads
1,118,861
Messages
5,574,705
Members
412,613
Latest member
EFRATA
Top