+ Reply to Thread
Results 1 to 6 of 6

Convert list of zip codes into ranges based on location assigned with no repeating zips

  1. #1
    Registered User
    Join Date
    08-07-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Convert list of zip codes into ranges based on location assigned with no repeating zips

    I tried using the process described in the earlier thread: "Convert list of zip codes into ranges" posted by Tragar, but I am getting repeated zip codes out of range (highlighted in yellow in the attached).

    I have a column with zip codes in numerical order, the next column includes the facility they are assigned to (with only a single letter for ease of use). I need to have the ranges for each location with beginning zip code - ending zip code. I am getting ranges that incldue other locations. Data with output and data with formulas below - and in attached file.
    Dest Zip FC Indicator Zip Code Range
    00501 A
    00544 A 00501 - 00544
    00601 C
    00602 C
    00610 C
    00611 C
    00612 C
    00613 C 00601 - 00613
    01772 B
    02495 B
    02532 B
    02534 B 01772 - 02534
    02535 C 00601 - 02535

    Dest Zip FC Indicator Zip Code Range
    00501 A =IF(B2=B3,"",INDEX(A:A,MATCH(B2,B:B,FALSE)) & " - " &A2)
    00544 A =IF(B3=B4,"",INDEX(A:A,MATCH(B3,B:B,FALSE)) & " - " &A3)
    00601 C =IF(B4=B5,"",INDEX(A:A,MATCH(B4,B:B,FALSE)) & " - " &A4)
    00602 C =IF(B5=B6,"",INDEX(A:A,MATCH(B5,B:B,FALSE)) & " - " &A5)
    00610 C =IF(B6=B7,"",INDEX(A:A,MATCH(B6,B:B,FALSE)) & " - " &A6)
    00611 C =IF(B7=B8,"",INDEX(A:A,MATCH(B7,B:B,FALSE)) & " - " &A7)
    00612 C =IF(B8=B9,"",INDEX(A:A,MATCH(B8,B:B,FALSE)) & " - " &A8)
    00613 C =IF(B9=B10,"",INDEX(A:A,MATCH(B9,B:B,FALSE)) & " - " &A9)
    01772 B =IF(B10=B11,"",INDEX(A:A,MATCH(B10,B:B,FALSE)) & " - " &A10)
    02495 B =IF(B11=B12,"",INDEX(A:A,MATCH(B11,B:B,FALSE)) & " - " &A11)
    02532 B =IF(B12=B13,"",INDEX(A:A,MATCH(B12,B:B,FALSE)) & " - " &A12)
    02534 B =IF(B13=B14,"",INDEX(A:A,MATCH(B13,B:B,FALSE)) & " - " &A13)
    02535 C =IF(B14=B15,"",INDEX(A:A,MATCH(B14,B:B,FALSE)) & " - " &A14)

    I want a list that does not have the first zip code repeated so that I can convert and sort to remove everything extraneous. Thank you - R.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Convert list of zip codes into ranges based on location assigned with no repeating zip

    need to have the ranges for each location
    I assume you mean you need a range of zip codes assigned to each FC?

    If so, you have a problem. How do you assign a range to facility C, when it has a non-contiguous set of assigned zip codes? That's why you apparently have highlighted the entry in cell C14 (for that matter, do not the entries in cells C17, C18, and most of all the rest in column C cause you concern?)

    I guess I don't know what you expect.

  3. #3
    Registered User
    Join Date
    08-07-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Convert list of zip codes into ranges based on location assigned with no repeating zip

    Hello mike65535
    I generally go back and "fix" the starting zip code to my Desired outcome column - which takes a huge amount of time when dealing with up to 80k zip codes. I am looking for an easy way to avoid doing it manually if possible. I have tried sorting in alternate ways, but still end up with the non-contiguous issue and zips included in incorrect ranges. There may not be a way to correct my problem - just checking if it is possible. Thanks - R.

    Dest Zip FC Indicator Zip Code Range Desired outcome
    00501 A
    00544 A 00501 - 00544
    00601 C
    00602 C
    00610 C
    00611 C
    00612 C
    00613 C 00601 - 00613
    01772 B
    02495 B
    02532 B
    02534 B 01772 - 02534
    02535 C 00601 - 02535 02535 - 02535
    02536 B
    02537 B
    02538 B 01772 - 02538
    02539 C 00601 - 02539 02539 - 02539
    02540 B
    02541 B
    02542 B
    02543 B 01772 - 02543 02540 - 02543
    02552 C 00601 - 02552 02552 - 02552
    02553 B 01772 - 02553 etc


    FINAL outcome to look like:
    FC Zip Range
    B 02540 - 02543
    C 02535 - 02535
    C 02539 - 02539
    C 02552 - 02552

  4. #4
    Registered User
    Join Date
    08-07-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Convert list of zip codes into ranges based on location assigned with no repeating zip

    Sorry - attachement included with updated column.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Convert list of zip codes into ranges based on location assigned with no repeating zip

    You are going to have to delve into the formula a bit - I think it needs to test if it needs to "reset" the start of the search array when Bn <> Bn-1 (right now it always starts at the top of the column so it always ends up with the first match)

    I'll have to play with it some to see if I can get that to work. Perhaps later. :-(

  6. #6
    Registered User
    Join Date
    08-07-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Convert list of zip codes into ranges based on location assigned with no repeating zip

    Thank you for any help you can give to me - I have been trying to work this out over the last few months every time the issue comes up and have not found a labor intensive way around it yet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Convert list of zip codes into ranges
    By tregan in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-08-2013, 12:41 AM
  2. Replies: 3
    Last Post: 02-12-2013, 10:16 AM
  3. Replies: 1
    Last Post: 02-04-2013, 03:01 AM
  4. Convert repeating data into a list
    By Peter33 in forum Excel General
    Replies: 6
    Last Post: 09-26-2009, 08:30 PM
  5. Need to convert list of 5 digit zip codes to ranges where possible
    By Mel07 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-14-2006, 07:43 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1