Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
"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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
have a cell return one of severl lists based on data from another | Excel Discussion (Misc queries) | |||
How do I validate data using different lists based on the data in. | Excel Discussion (Misc queries) | |||
Format text based on which of several unique lists the value belongs to? | Excel Worksheet Functions | |||
finding common numbers in large lists | Excel Worksheet Functions |