Need help on converting a list of Canadian Postal code to ranges
I am trying to figure out how to convert a list of Canadian postal codes into ranges. The list I have is over 800k lines. My goal is to combine any sequential postal codes that fall within the same city into a range, and any stand alone postal code will be entered as a "range" by itself.
1st chart is a snippet of my raw data
2nd chart is how I want it to look (from and to postal codes / city)
Please Login or Register to view this content.Please Login or Register to view this content.
Last edited by 6StringJazzer; 03-27-2024 at 01:57 PM. Reason: fixed data format to be readable
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.
Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/
I am providing the link because it's your first post.
https://www.mrexcel.com/board/thread...-zone.1256285/
As an example, I don't see how this is a "range" or how it is derived from the first list.
T0A 0A0 T0A 0A0 ABEE, AB
It would be its own "range" (from and to) as I do not want to include anything in between T0A 0A0 and T0A 0E1
I don't see how any of them are a "range". Please pick any one of them and explain how it is derived from the first list.
for example:
Acheson, AB - (from) T7X 6A1 will be the lower limit and (to) T7X 6A9 will be the upper limit.
Acheson, AB - (from) T7X 6B1 will be the lower limit and (to) T7X 6B9 will be the upper limit.
and so on
Whereas Acme, AB will be its own range because T0M 0A1 and T0M 0A2 does not exist on my data. So it will show as:
T0M 0A0 (From postal code) - T0M 0A0 (To postal code) - Acme, AB
T0M 0A3 (From postal code) - T0M 0A3 (To postal code) - Acme, AB
Now I see what's going on. Your data was not formatted so it just looked like gobbledy-gook to me because I am not familiar with Canadian postal code formats. (A quick explanation of that up front could have saved time.) When you just paste in data, all spaces and tabs are compressed out when the post is displayed.
How do you define "sequential"? Why don't you consider (for Acheson) T7X 6A9 to T7X 6B1 be sequential? Is there a T7X 6B0 that is not included in that range?
I think this is going to require VBA. Can you provide a somewhat larger sample set for testing? .xlsx file attachments are limited to 1MB, .zip files are limited to 9.77MB.
For Acheson, I cannot consider T7X 6A9 to T7X 6B1 as a sequence as I do not have T7X 6B0 included in the data.
I have attached a sample data
Here is a trial solution. I think your sample output has an error. Your sample output has this row
Values as displayed
Output
but the data does not include T7X 6B5, so that range has to be split into two.
Values as displayed
RAW DATA
With a Helper column in column C,
C2=IFERROR(IF(AND(LEFT(A2,6)=LEFT(A1,6),OR(--RIGHT(A2,1)=--RIGHT(A1,1)+1)),C1,C1+1),1), copy down.
From Postal Code,
E2=XLOOKUP(SEQUENCE(MAX($C$2:$C$1224)),$C$2:$C$1224,$A$2:$A$1224,,0,1)
To Postal Code,
F2=XLOOKUP(SEQUENCE(MAX($C$2:$C$1224)),$C$2:$C$1224,$A$2:$A$1224,,0,-1)
City,
G2=XLOOKUP(SEQUENCE(MAX($C$2:$C$1224)),$C$2:$C$1224,$B$2:$B$1224,,0,1)
Formulas for earlier Excel version in workbook.
Here is a solution for the whole table including the headers with a single cell formula (everything in one go; No copy needed):
Please empty all expected results and try:Please Login or Register to view this content.
this works!! thank you so much!!!
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
You are Welcome!
Thanks for the feedback and rep. Glad to have helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks