Announcement

Collapse
No announcement yet.

Display Related Information By Postcode

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Display Related Information By Postcode



    I have a spreadsheet which we use to work out courier delivery times within
    Australia. Each row has Postcode, Suburb, State, Courier company, Delivery time,
    Pickup time. ETC. There are 17236 rows ..

    In the first column on the spreadsheet we have every postcode in Australia. The
    second column has every suburb in Australia. Most postcodes cover multiple suburbs.
    I.E 2083 is Bar point, Cogra bay, Dangar Island, Milsons passage, Mooney Mooney,
    Mooney Mooney creek and Brooklyn.

    I have written a Macro which involves inputting the postcode then using the
    vlookup function and some cross referencing cells, the macro returns the Suburb, the
    Courier, The earliest pickup date, and the pickup time.

    My problem is vlookup will only return the first or last suburb that is in a
    postcode array so if I put 2083 I can get it to return Bar point or Brooklyn but
    not the rest..

    In the above example the first 4 suburbs there is no courier (post office). The
    5th & 6th are one courier company the last two are another. Also the pickup time
    and dates vary.

    I do have some code where I can return all the suburbs in a string but its no use
    for cross referencing with other cells to return the info I really need..

    $20.00
    Attached Files
    Last edited by Dave Hawley; October 31st, 2008, 17:05.

  • #2
    Re: Postcodes

    Hi,

    Do you prefer a formula based approach or VBA?
    Kris

    ExcelFox

    Comment


    • #3
      Re: Postcodes

      Sounds like a perfect job for a PivotTable. PM me the amount you are willing to pay and I'll unlock the Thread.

      Let me know what you think it is worth ?
      That is NOT how this forum works, YOU state the amount you are willing to pay.

      Comment


      • #4
        Re: Display Related Information By Postcode

        Thread re-opened and Westerntribal is willing to pay $20.00

        Comment


        • #5
          Re: Display Related Information By Postcode

          Do you prefer a formula based approach or VBA?

          Doesnt matter as long as it works..

          Comment


          • #6
            Re: Display Related Information By Postcode

            Hi,

            See attached.

            HTH
            Attached Files
            Kris

            ExcelFox

            Comment


            • #7
              Re: Display Related Information By Postcode

              Hi,

              Correction in the formula.

              In P2 on sheet2 should be

              =IF(A2<>"",IF(A2=pCode,LOOKUP(9.999999E+307,$P$1:P1)+1,""),"")

              and copied down.
              Kris

              ExcelFox

              Comment


              • #8
                Re: Display Related Information By Postcode

                Thanks Kris this does the job.

                Much appreciated

                Comment


                • #9


                  Re: Display Related Information By Postcode

                  Hi Chris,

                  Thanks Kris this does the job.
                  You are welcome!!

                  Got the payment.

                  Regards,
                  Kris

                  ExcelFox

                  Comment

                  Working...
                  X