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

1. ## 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.

2. ## 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. ## 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. ## Re: Convert list of zip codes into ranges based on location assigned with no repeating zip

Sorry - attachement included with updated column.

5. ## 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. ## 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.

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

#### 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