Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extended List (2007)

    Would anyone have any ideas on a clever (quick) way to take the postcodes and associated zone code in the attached Postcode to Zone List worksheet and expand it into a list suitable for a vertical lookup. The second worksheet (Lookup List) shows the end result I am looking for.

    Thanks

    Alex
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extended List (2007)

    Could you please post as a verison earlier than XL2007 for those of us without XL2007 or you will be limited in responses....

    Steve

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extended List (2007)

    Sorry, here's another version.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extended List (2007)

    Do you want to return an error value if the post code can't be found? If that doesn't matter, you can use the first post code in each cell. See attached version.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Sep 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extended List (2007)

    I don't think I got my request across too clearly. The list I am trying to build would have every post code from the Postcode to Zone List worksheet listed in a column, including those that are summarised between a start and end value. For example, in row 16, 0850-0854 would need to be expanded to list all the postcodes in that range with the zone code (being 27 in this case) listed in the adjacent column.

    Does that make more sense?

    Regards
    Alex

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extended List (2007)

    If you need the expanded list for other purposes, it would be possible to write a macro for that. But if it is for lookup purposes only, you don't need to expand the list - the workbook I attached shows how to use VLOOKUP without expanding the list.

    BTW why do you have overlapping ranges in the list?

  7. #7
    New Lounger
    Join Date
    Sep 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extended List (2007)

    I have a workbook with freight consignment data. One of the columns is the postcode of the consignment destination. I need to look up that postcode in a list to find out the zone the freight carrier has allocated to that postcode. That is what I am trying to create the expanded list for. With the list on the Postcode to Zone List worksheet (as supplied by the freight carrier) I can't look up a postcode such as 0801, for example.

    If you can suggest or devise a macro that would expand the list I'd appreciate it. As a consultant in the freight industry I work with these lists quite often - new carriers come along and they all have different postcode to zone structures, and then existing carriers also change their zone structures from time to time.

    I had not noticed the overlapping ranges - are you referring to rows 29-31? I'd probably have to clean those up before running the macro.

    Regards
    Alex

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extended List (2007)

    Sorry to be a nuisance, but the workbook I attached higher up in this thread does show how you can look up a code such as 801 or 2270 without expanding the entire list.

    Anyway, here is a macro that will create an expanded list in columns D and E:

    Sub ExpandPostCodes()
    Dim r As Long
    Dim m As Long
    Dim s As String
    Dim p As Integer
    Dim c1 As Long
    Dim c2 As Long
    Dim c As Long
    Dim t As Long
    t = 1
    m = Cells(Rows.Count, 1).End(xlUp).Row
    For r = 2 To m
    s = Cells(r, 1).Value
    p = InStr(s, "-")
    If p > 0 Then
    c1 = Val(Left(s, p - 1))
    c2 = Val(Mid(s, p + 1))
    Else
    c1 = Val(s)
    c2 = Val(s)
    End If
    For c = c1 To c2
    t = t + 1
    Cells(t, 4) = c
    Cells(t, 5) = Cells(r, 2)
    Next c
    Next r
    Range("D1:E" & t).Sort Key1:=Range("D1"), Header:=xlYes
    End Sub

  9. #9
    New Lounger
    Join Date
    Sep 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extended List (2007)

    Sorry to you Hans. I didn't understand what you had done in the first response. Actually, I still don't understand how the =VALUE(IF(ISERROR(FIND("-",A2)),A2,LEFT(A2,FIND("-",A2)-1))) formula finds the postcodes 'hidden' within a xxxx-xxxx range. Nevertheless, the macro works just great and has created the list I was looking for.

    Excellent work. Many thanks.

    Regards
    Alex

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extended List (2007)

    The formula extracts the first postcode pppp in a range pppp-qqqq.
    The lookup formula uses the "approximate" version of VLOOKUP: searching stops at the highest value that is less than or equal to the value you're searching for. So for postcode=801, the search stops at 800 (because 801 itself cannot be found) and returns the corresponding zone.

  11. #11
    New Lounger
    Join Date
    Sep 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extended List (2007)

    Hans, thanks for that explanation. Now I see why you asked whether an error code needs to be returned if the postcode can't be found. In this case it does otherwise the formula will generate a postcode/zone combination that may be incorrect. I found this out by testing this method after I found some problems with the macro generated list.

    The macro creates some duplications, which I have highlighted red in the attached file. The duplicates are given different zone numbers - usually 4 or 9 from what I have noticed. I have deleted a lot of the expanded list to fit under the 100k attachment limit.

    Any clues?

    Regards
    Alex
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extended List (2007)

    Here is an example of harmless overlap in the original table:

    <table border=1><td align=center>2084-2200</td><td align=center>1</td><td align=center>2179-2190</td><td align=center>1</td></table>
    The range 2179-2190 is entirely contained within the range 2084-2200. Since both specify zone 1 there is no conflict.

    Here is a potential cause of problems - it is part of the original list, sorted by zone:

    <table border=1><td align=center>3525</td><td align=center>4</td><td align=center>2527</td><td align=center>4</td><td align=center>2529-3531</td><td align=center>4</td><td align=center>3533</td><td align=center>4</td></table>
    I suspect that 2527 and 2529 should have been 3527 and 3529, respectively, to fit with the rest. The range 2529-3531 now overlaps with lots of other ranges that belong to different zones.

    You should check very carefully for problems like this, correct values if necessary, then run the macro again.

  13. #13
    New Lounger
    Join Date
    Sep 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extended List (2007)

    Thanks for your prompt response. A thorough check and correction of the ranges has allowed the macro to now create a clean list - no garbage in, no garbage out!!

    Regards
    Alex

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •