Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Display Related Information By Postcode

  1. #1
    Join Date
    17th November 2007
    Posts
    3

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Dave Hawley; October 31st, 2008 at 17:05.

  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Postcodes

    Hi,

    Do you prefer a formula based approach or VBA?

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    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.

  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    Re: Display Related Information By Postcode

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

  5. #5
    Join Date
    17th November 2007
    Posts
    3

    Re: Display Related Information By Postcode

    Do you prefer a formula based approach or VBA?

    Doesnt matter as long as it works..

  6. #6
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Display Related Information By Postcode

    Hi,

    See attached.

    HTH
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  7. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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.

  8. #8
    Join Date
    17th November 2007
    Posts
    3

    Re: Display Related Information By Postcode

    Thanks Kris this does the job.

    Much appreciated

  9. #9
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Display Related Information By Postcode

    Hi Chris,

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

    Got the payment.

    Regards,

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Return Related Information Chosen From Drop-down
    By ataylor in forum Excel General
    Replies: 3
    Last Post: December 5th, 2007, 04:10
  2. Copy Over Related Information
    By weekendrockstar in forum Excel General
    Replies: 1
    Last Post: December 4th, 2007, 10:23
  3. Display Related Data For Chosen Item
    By cs2007 in forum Excel General
    Replies: 5
    Last Post: November 20th, 2007, 16:05
  4. Display Related Data From Chosen Value
    By 1ceman in forum Excel General
    Replies: 4
    Last Post: November 6th, 2006, 14:01
  5. Charting: Display related comment dynamically
    By iwrk4dedpr in forum Excel General
    Replies: 6
    Last Post: October 12th, 2005, 04:29

Bookmarks

Posting Permissions

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