Regrouping large lists into areas based on zip codes
 

I receive lists of about 200 addresses covering about 40 zip codes. I have
to separate and re-group this list into 7 areas based on zip codes (which
aren't serial, of course). I've considered using IF.THEN..ELSE formulas,
but fear that would be too complex. Does anybody have any simple, elegant
solution?



Thanx

We'd need to know what your 7 classifications are. Assuming, for
instance, that the seven classes are based on the first 3 characters in
the zip code and assuming the zip code is in a cell by itself, you
could use a MID() or LEFT() function to derive just those first 3
characters, and then sort.

If you could post a Before and After view of how the data is presented
now and how you want it to look that would be ideal.

"Morris DS" wrote in message
...
I receive lists of about 200 addresses covering about 40 zip codes. I have
to separate and re-group this list into 7 areas based on zip codes (which
aren't serial, of course). I've considered using IF.THEN..ELSE formulas,
but fear that would be too complex. Does anybody have any simple, elegant
solution?



Thanx



I'd process assign a Group# to a spare column using a Select...Case in VBA
then Autofilter on the Group# column for the 7 reports.
Presumed zipcodes in column-A

sub test()
'presume column-A are you zipcodes
'presume column-H is free to hold Group#

'I always copy the raw data elsewhere for manipulation just-in-case
something goes wrong
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Report"

'find last row of data in your longest column, ie: column-A
finalrow = range("A65536").end(xlup).row

'ensure all zipcodes have a value
Range("A1:A" & finalrow).SpecialCells(xlCellTypeBlanks).Value = 0

for rowindex = 2 to finalrow
testvalue = trim(range("A" & rowindex).value) 'trim any
leading/trailing spaces from zipcode
select case testvalue
'presume column-h is available to be a sort column
case 11111: range("h" & rowindex).value = 1
case 11112: range("h" & rowindex).value = 2
<etc for all 40 zip codes
case else:
range("h" & rowindex). value = 0
rows(rowindex).Font.ColorIndex = 7 'turn row purple to
make errors easily spotted
end select
next
end sub

You can now Autofilter on Column-H to work with the individual groups.
If you prefer having each Group moved to its own sheet, add the following
before the "end sub" above.

Sheets.Add.Name = "Group1"
Sheets.Add.Name = "Group2"
<etc for all groups

For Each WS In Worksheets(Array(1,2,3,4,5,6,7))
WS.Range("A1:G1").Value = Sheets("Print").Range("A1:G1").Value 'copy title
row to each Group
Next

Sheets("Report").Activate
For Each cell In Range("H1:H" & Range("H65536").End(xlUp).Row) 'the Group
column
Select Case cell.Value
Case 1:
cell.EntireRow.Cut Sheets("Group1").Range("A65536").End(xlUp).Offset( 1,
0)

Case 2:
cell.EntireRow.Cut Sheets("Group2").Range("A65536").End(xlUp).Offset( 1,
0)

<etc for all 7 Groups
End Select
Next

If you want, email before/after sample layouts to jefgorbach at aol.com and
I'll try to get you coded over the weekend.