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