Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to convert list of 5 digit zip codes to ranges where possible
I have been unable to figure this one out. I have over 2000 zip codes in a single column (One 5 digit zip code per row) which are already sorted in numeric order. I need to put these individucal zip codes into ranges where possible when zip codes are sequential (i.e. 91714-91734). Is this possible outside of doing this manually because it's killing me!!! Help. -- Mel07 ------------------------------------------------------------------------ Mel07's Profile: http://www.excelforum.com/member.php...o&userid=32458 View this thread: http://www.excelforum.com/showthread...hreadid=522385 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to convert list of 5 digit zip codes to ranges where possible
A couple questions. Are the zip codes numeric or text? And what do you want
if there's a loner? If the codes are numeric, and loners can be shown as 'groups of one' (ex 22033-22033), then here's one approach. If the zip codes are in column A and sorted in ascending order, beginning in row 2, use column B to designate what group each zip belongs to. In B2 enter the number 1. In B3 enter the formula =IF(A3=A2+1,B2,B2+1). Then in column C we'll create the ranges. In C2 enter the formula =IF(B2=B3,"",INDEX(A:A,MATCH(B2,B:B,FALSE)) & " - " & A2). Copy that down to C3. Then select B3:C3 and autofill down. HTH. --Bruce "Mel07" wrote: I have been unable to figure this one out. I have over 2000 zip codes in a single column (One 5 digit zip code per row) which are already sorted in numeric order. I need to put these individucal zip codes into ranges where possible when zip codes are sequential (i.e. 91714-91734). Is this possible outside of doing this manually because it's killing me!!! Help. -- Mel07 ------------------------------------------------------------------------ Mel07's Profile: http://www.excelforum.com/member.php...o&userid=32458 View this thread: http://www.excelforum.com/showthread...hreadid=522385 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to convert list of 5 digit zip codes to ranges where possible
As alternative to Bruce's suggestion, here's a macro that might do the trick
Sub zips() Dim rng As Range Dim strFirst As String Dim intCurr As Long Dim strlast As String Dim rngTgt As Range Dim x As Integer Dim lTest As Long Range("B1:B100").ClearContents Set rngTgt = Range("B1") strFirst = "" strlast = "" intCurr = 0 For Each rng In Selection lTest = CLng(rng) If (lTest intCurr + 1) Then ' no sequence, so write what you have If Len(strlast) = 0 Then rngTgt.Offset(x, 0) = strFirst x = x + 1 Else rngTgt.Offset(x, 0) = strFirst & " - " & strlast x = x + 1 End If strFirst = rng intCurr = CLng(strFirst) strlast = "" Else ' it's still a sequence so increase the upper end and ' the comparison value strlast = rng intCurr = CLng(rng) End If Next End Sub For information on installing the code see Getting Started with Macros and User Defined Functions http://www.mvps.org/dmcritchie/excel/getstarted.htm "Mel07" wrote: I have been unable to figure this one out. I have over 2000 zip codes in a single column (One 5 digit zip code per row) which are already sorted in numeric order. I need to put these individucal zip codes into ranges where possible when zip codes are sequential (i.e. 91714-91734). Is this possible outside of doing this manually because it's killing me!!! Help. -- Mel07 ------------------------------------------------------------------------ Mel07's Profile: http://www.excelforum.com/member.php...o&userid=32458 View this thread: http://www.excelforum.com/showthread...hreadid=522385 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to convert list of 5 digit zip codes to ranges where possible
Hi Bruce, Thanks for the reply - never done one of these forums actually I'm hoping your input will help me get throught this work faster so thanks! Anyway, the zip codes are numeric. If there's an unique, loner zip code then I would like to have it simply input it again in the column next to it, column B. I'm going to give your formula a shot. I appreciate the help greatly! I'll let you know if it works. -- Mel07 ------------------------------------------------------------------------ Mel07's Profile: http://www.excelforum.com/member.php...o&userid=32458 View this thread: http://www.excelforum.com/showthread...hreadid=522385 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update data validation list with new entries?? | Excel Discussion (Misc queries) | |||
HOW CAN I CONVERT EXCEL DATA TO BAR CODES? | Excel Discussion (Misc queries) | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
convert excel list to pivot table | Excel Discussion (Misc queries) | |||
Viewing List of Named Ranges | Excel Discussion (Misc queries) |