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

• 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.

Quote

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.

• Re: Display Related Information By Postcode

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

• Re: Display Related Information By Postcode

Do you prefer a formula based approach or VBA?

Doesnt matter as long as it works..

• 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.

• Re: Display Related Information By Postcode

Thanks Kris this does the job.

Much appreciated

• Re: Display Related Information By Postcode

Hi Chris,

Quote

Thanks Kris this does the job.

You are welcome!! :cheers:

Got the payment.

Regards,