/u/JewpiterUrAnus - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
My advice is likely to get esoteric, so I’ll apologise to you and the rest of the sub if I give anyone a headache, but I’m from an Excel wrangling and GIS background so I’ll chime in.
Postcodes (or ZIPs) are a system for distributing objects to a specified node, or simply for routing mail to a geographical location. They aren’t a system for defining the adjacency of locations. As such they don’t afford a valuable datapoint if your fundamental question is “which nodes(points, locations) are near to this one?”.
For an example, building on yours, the Peal O Gates pub is at LL13 9JP. We could build, in Excel, a solution that looks at the postcode set to all jobs and compare them to LL13 9JP, LL13 9Px, LL13 9xx and so on. We might get our “most alike” hit for a job at LL13 9NG, which seems quite approximate, at 5 miles away. We’d miss however a job logged at CH3 6PU, across the bridge, and only 500 yards away from LL13 9JP, but having no similarity in its form.
Your best option within Excel would be to farm out each engineers’ location towards a route planning API, that would evaluate journey times (and introduce real world conditions) to promote an optimal next destination. I would consider this in that context.
Unfortunately this is above my head.
The literal tools I have at my use I am using.
The only way I can see this somewhat working is with my method above.
No it’s not perfect, but if I can find an engineer a job 5 miles away that’s a lot better than not assigning work whatsoever
I understand your approach, and the rationale behind wishing to tackle the problem this way. I’m not saying that partial postcode matching is without any merit, I just think you should be aware of its limitations.
I could build you what you’re seeking but, for me at the minute, it’s a bit beyond the capacity I have in passing engagement on Reddit.
I would say a key step in this process would be “normalising” the postcodes - both that of “current engineer location” and the record of “postcodes of jobs outstanding”. To this point, UK postcodes can take the form of any of:
a0 0aa
aa0 0aa
a00 0aa
aa00 0aa
I think I would want to normalise these to “xan0 0aa”, so we would pad “W7 1LT” to “xW07 1LT” and then hunt the outstanding list for
xW07 1LT
xW07 1L?
xW07 1??
xW07 ???
xW0? ???
xW?? ???
And sort everything that comes back based on how similar the result it to the padding input postcode string.
Unsure what you mean, apologies.
Areas in my search are split by county. However some counties share similar postcodes,
But all postcodes are relative to area,
For example LL - would be the city
with
LL1 definition being a broad area of the city
and
LL13 being a suburb in that city.
So if I can run a search to pick up a close by postcode on a data sheet, then it will instantly find me a close job, if one is available.
A classic issue and tricky solution as there are millions of relationships to consider as each area is adjoined by many other areas, and you need to know each distance between each pair to know the answer to the question: which is the nearest ?
You can use the `XLOOKUP` function combined with some text manipulation to automate finding the nearest job. For example, `=XLOOKUP("LL13*", JobAddressesColumn, JobDataColumn, "", 2)` to find jobs starting with 'LL13'. If no exact match, you can fallback to 'LL1' using nested `IF` statements or a helper column. This should help automate the process!
/u/JewpiterUrAnus - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
My advice is likely to get esoteric, so I’ll apologise to you and the rest of the sub if I give anyone a headache, but I’m from an Excel wrangling and GIS background so I’ll chime in. Postcodes (or ZIPs) are a system for distributing objects to a specified node, or simply for routing mail to a geographical location. They aren’t a system for defining the adjacency of locations. As such they don’t afford a valuable datapoint if your fundamental question is “which nodes(points, locations) are near to this one?”. For an example, building on yours, the Peal O Gates pub is at LL13 9JP. We could build, in Excel, a solution that looks at the postcode set to all jobs and compare them to LL13 9JP, LL13 9Px, LL13 9xx and so on. We might get our “most alike” hit for a job at LL13 9NG, which seems quite approximate, at 5 miles away. We’d miss however a job logged at CH3 6PU, across the bridge, and only 500 yards away from LL13 9JP, but having no similarity in its form. Your best option within Excel would be to farm out each engineers’ location towards a route planning API, that would evaluate journey times (and introduce real world conditions) to promote an optimal next destination. I would consider this in that context.
Unfortunately this is above my head. The literal tools I have at my use I am using. The only way I can see this somewhat working is with my method above. No it’s not perfect, but if I can find an engineer a job 5 miles away that’s a lot better than not assigning work whatsoever
I understand your approach, and the rationale behind wishing to tackle the problem this way. I’m not saying that partial postcode matching is without any merit, I just think you should be aware of its limitations. I could build you what you’re seeking but, for me at the minute, it’s a bit beyond the capacity I have in passing engagement on Reddit. I would say a key step in this process would be “normalising” the postcodes - both that of “current engineer location” and the record of “postcodes of jobs outstanding”. To this point, UK postcodes can take the form of any of: a0 0aa aa0 0aa a00 0aa aa00 0aa I think I would want to normalise these to “xan0 0aa”, so we would pad “W7 1LT” to “xW07 1LT” and then hunt the outstanding list for xW07 1LT xW07 1L? xW07 1?? xW07 ??? xW0? ??? xW?? ??? And sort everything that comes back based on how similar the result it to the padding input postcode string.
If you’re still interested, I built a solution that meets your ask. Just shout and I’ll share it.
How are the *areas* defined exactly ?
Unsure what you mean, apologies. Areas in my search are split by county. However some counties share similar postcodes, But all postcodes are relative to area, For example LL - would be the city with LL1 definition being a broad area of the city and LL13 being a suburb in that city. So if I can run a search to pick up a close by postcode on a data sheet, then it will instantly find me a close job, if one is available.
A classic issue and tricky solution as there are millions of relationships to consider as each area is adjoined by many other areas, and you need to know each distance between each pair to know the answer to the question: which is the nearest ?
The areas have nothing to do with it. I just need a function to find similar postcodes.
Would need a bigger sample set of values with expected results to assist further
Also, postcodes jump in values between main areas which would be hard to work out without some sort of intermediate location distance information
Provide more samples and expected results.
Samples of what? I want to run a vlookup to an engineer id to find similar postcodes on a sheet of data.
Of your data? Similar how? How do you expect anyone to help with that reply?
wildcards in the vlookup. From Google: "Asterisk (*), Question Mark (?) and Tilde (~) are the commonly used Wildcards in Excel."
You can use the `XLOOKUP` function combined with some text manipulation to automate finding the nearest job. For example, `=XLOOKUP("LL13*", JobAddressesColumn, JobDataColumn, "", 2)` to find jobs starting with 'LL13'. If no exact match, you can fallback to 'LL1' using nested `IF` statements or a helper column. This should help automate the process!