Sorting

t8ntlikly
NewLounger
Posts: 23
Joined: 26 Jan 2010, 00:03

Sorting

Post by t8ntlikly »

I downloaded a POI file of BOA locations for my Garmin. The problem is that it has almost all of the locations across the US. 9515 locations to be exact.
I have managed to get it to WA, ID, OR, MT (somewhat) but I can't get it sorted so just those states show up.It is interspersed with MI, AL, AK, just to name a few. What am I doing wrong here and how can I do this? What I want to be able to do is sort this by City, State. so I can upload them to my Garmin as separate POI Files
I am attaching an amended spreadsheet in .xls format.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sorting

Post by HansV »

See the attached version. I used some intermediate formulas to extract the city and state, so you can sort on them.
Amended BOA POI WA OR.xls
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

t8ntlikly
NewLounger
Posts: 23
Joined: 26 Jan 2010, 00:03

Re: Sorting

Post by t8ntlikly »

Thank you so much Hans. I was able to sort out everything except WA,OR,ID, and MT, which is what I wanted. I have no clue as to what all of those formulas are about, but I read up on them. Still don't have a clue :scratch:
Now all I should have to do is put each state location into its own .csv file and download them into my Garmin Waypoints. I will let you all know how that works out :grin:
You are an ACE !

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sorting

Post by HansV »

The formulas in column E return the position of the line break CHAR(10) in column D.
The formulas in column F return the position 7 characters before the last character in column D, i.e. the start of the state.
The formulas in column G return the part of the text in column D starting after the line break, and ending before the start of the state, i.e. the city.
The formulas in column H return 2 characters of the text in column D from the start of the state, i.e. the state abbreviation.
Best wishes,
Hans

t8ntlikly
NewLounger
Posts: 23
Joined: 26 Jan 2010, 00:03

Re: Sorting

Post by t8ntlikly »

Ok, that makes some sense but me thinks I've got some reading up to do.
So let me ask you this: How would it look if I wanted to show the zip codes?

John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sorting

Post by HansV »

The zip code is easy: it consists of the last 5 characters in column D (assuming they are all 5 digit zip codes). So you can enter

=RIGHT(D1,5)

in a cell in row 1, say in I1, and fill down as far as needed.
Best wishes,
Hans