Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Sorting A large list into smaller lists on different sheets

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sorting A large list into smaller lists on different sheets

    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/nuqscmvu1f...stem.xlsx?dl=0

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,266
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Sorting A large list into smaller lists on different sheets

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #3
    New Member
    Join Date
    Jun 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting A large list into smaller lists on different sheets

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

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,266
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Sorting A large list into smaller lists on different sheets

    Have a look here to see how to install & run macros.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  5. #5
    New Member
    Join Date
    Jun 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting A large list into smaller lists on different sheets

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

  6. #6
    New Member
    Join Date
    Jun 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting A large list into smaller lists on different sheets

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

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,266
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Sorting A large list into smaller lists on different sheets

    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  8. #8
    Board Regular
    Join Date
    Jun 2002
    Location
    Abergavenny, Wales, UK
    Posts
    251
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting A large list into smaller lists on different sheets

    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:
    Zip Territory
    03087 Winchester
    03079 Winchester
    03053 Winchester
    01701 Winchester
    01701 Winchester
    01702 Winchester
    01703 Winchester
    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
    Technology 4 U

  9. #9
    New Member
    Join Date
    Jun 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting A large list into smaller lists on different sheets

    Perfect thank you again!

  10. #10
    Board Regular
    Join Date
    Jun 2002
    Location
    Abergavenny, Wales, UK
    Posts
    251
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting A large list into smaller lists on different sheets

    Hi Fluff

    Very nice piece of code.
    Had I seen your response, I wouldn't have bothered with my Pivot Table solution.
    --
    Roger Govier
    Technology 4 U

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •