Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

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

                  Trending

                  Collapse

                  • maichal
                    auto generate id in user form save sale purchase data on sale & purchase sheet
                    maichal
                    i am add add sale transaction & add purchase transaction command button & the id was Auto generate in text box 1 if i am choose sale in combo box 1 than sale-001 id was Auto generate in text box 1 add the name in combo box 2 & click on add sale transaction command button the data was add on the sale sheet,if i am choose Purchase in combo box 1 than Purc-001 id was Auto generate in text box 1 add the name in combo box 2 & click on add Purchase transaction command button the data was add on the Purchase sheet, please solve this sir, after add the information in sale & purchase excel sheet, i am choose sale in combo box 1 the id was auto generate in id text box 1, than i am choose the name in combo box 2, if i am choose sale in combo box 1 than combo box 2 show only sale Customer...
                    2 days ago
                  • DiogoCuba
                    US$20 to Automate Crew Roster
                    DiogoCuba
                    Hi, I have a Crew Roster that controls the crew changes of a certain vessel and I would like to automate it to avoid wasting time with manual copy and paste.

                    Anybody willing to help me?

                    Some of the actions I need:
                    • Create a Pax list and a Flight Manifest based on the personnel assigned to embark and disembark;
                    • Generate a Daily POB based on the names that are onboard (Including the dates of embark and a counter to count how many days the person is onboard);
                    • Compare the Daily POB to a criteria to see if the safe manning is compliant or not;
                    • Create a PAX LIST for the next 7 days automatically so that I can see who is crew change and who is not - this should be triggered based on the dates;
                    • Auto generate Flight Manifest when I click a button and input the
                    ...
                    June 27th, 2017, 09:47
                  Working...
                  X