POI Creation tips

Good post, metricman! This would make a very good FAQ.

Thanks

dobs108

Very good information
Thank you

The address should be complete. It is also very handy to have a phone number so you can call the business.

Don't you just hate trying to find an entry in your spread sheet to update it. You try to sort the file, but all you can do is sort on the street address. Not much help on very large files.

Well, here's a little trick my wife showed me to use in Excel (should work in Open Office also). This will not work well, if the phone number is the last item in the "D" cell or you have put in the full 9 digit Zip Code (more on that later).

This is written for newbies.

1. Before starting, save a back-up copy.

2. The 5 digit Zip Code MUST be the last 5 characters in the "D" column. NO SPACES OR PHONE NUMBER AT THE END OF THE CELL.

3. In the "E" column, left click in the "E1" cell. (cell should now be highlighted with a thicker cell border).

4. Left click in the "Formula" entry area. (text entry area above work area)

5. In the formula entry area type:

=RIGHT(D1,5)

and then press the "Enter" key.
Note: What you told the program to do was: Starting from the right, in cell D1, grab 5 characters and put them in this cell (E1).

6. The Zip Code from cell D1 should now be displayed in E1. If not check your typing and correct. Also check for hidden characters at end of line in cell D1.

7. Left click once in cell E1.

8. Right click on E1 and select "Copy".
Result: Cell is now blinking.

9. Select all cells from E2 to the last "E" cell in your file.

10. Right click on the highlighted cells and select "Paste".
Result: Zip Code is now displayed in all the "E" cells that were highlighted.

11. Scroll down the E column and check for short or missing Zip Codes. Edit the "D" cell to correct. "E" changes when you press enter or "Tab" when done.

12. Select the "Select all cells" button at the junction of the Row and column headers in the upper left of the work area.
Result: The entire spreadsheet is now highlighted.

13. Select "Data" Tab at top, then select "Sort"

14. Select Column "D" in the left dropdown menu in the pop-up window.
Note: "Sort on" set to "Values. "Order" set to "A to Z". Both are the default settings.

15. Select "OK" in the pop-up window.
Note: If you get a "Sort Warning" window, ensure that it is set to "Sort anything that looks like a number, as a number" and select "OK"

16. File should now be sorted by Street address.

17. Select "Data" then select "Sort"

18. Select Column "E" in the left dropdown menu in the pop-up window.
Note: "Sort on" set to "Values. "Order" set to "A to Z". Both are the default settings.

19. Select "OK" in the pop-up window.
Note: If you get a "Sort Warning" window, ensure that it is set to "Sort anything that looks like a number, as a number" and select "OK"

20. File is now sorted by Zip Code and if there are multiple entries for a Zip Code, then the lines within that Zip Code are sorted by street address.

The nice thing about doing this, is that should you insert or delete a line, Excel will adjust all the formulas in the "E" column.

Doing this helped me reduce the number of multiple entries. BofA has some incorrect Zip Codes listed at their website and that can add confusion if you "correct" them and later forgot that you did that. You wind up with duplicate entries that have different Zip Codes. Using Extra POI Editor will help weed those out.

Note: If you have the phone number as the last line in the "D" cells and you don't want to edit every cell, you can adjust the value in the formula. Change "=RIGHT(D1,5)" to "=RIGHT(D1,12)" (without the quotes) and try that. Tweak, if needed.

Hope this helps some of you that are managing a large POI file. It sure helped me.

Note: You can't use the Search function on colemn "E". You'll have to search for the Zip Code in column "D".

IMPORTANT - Be sure to delete column "E" just before you generate the .csv file. It will not work with the POI-Factory software if column "E" is present in the .csv file. The .csv file will have the extra column in it with the Zip Code in it (not the formula) and the software will not output a complete .csv file - I know from experience. If you had the working file saved as a .xls file, you won't lose anything.

Have fun!

Thanks! Great info.

Tip: The ASAP Utilities Excel add-in is a great tool for manipulating spreadsheet content; e.g. joining columns. It's free for home use.

I'll check it out,

There are all kinds of built in tools and add-ons that one can use. This is really a very simple way of sorting very large files that happen to be formatted the right way. It takes about 20 seconds to do it, even though it seems longer when you read how to do it (remember, I wrote it for newbies, so there is a lot of detail in the steps).

Luckily for me, the BofA site has the addresses formatted in 2 lines, so that cut down on the editing. It made this a convenient way to do it. Those that don't need "All" the locations can easily edit the .csv file by using the Zip Code(s), as it is already sorted.

Now that the BofA POI is done, I can start to learn how to use EPE and be able to do the updates easier, faster and have less errors.

I never resort my files, I create them in the same order as the locations are listed on the company's website. If I need to locate a particular one, I just hit CTRL-F and type it in. This way I don't mess up my "order" and makes it very easy to maintain as my order remains the same as the company's website.

Works well also.

Rath *loves excel*

Thanks for the insights. I'll have to try POI construction.

That would be nice if the BofA site had a list - at least I can't find one and blind people are reading my emails requesting a list.

The BofA site just doesn't work that way. You enter an address, a city, state or zip code. It will find up to 50 locations within 100 miles and it lists the 10 closest at a time and displays those 10 on a Bing map. The next 10 on the next page and map are further away - and so on and so on.

I used Ctrl-f alot myself. I searched by zipcode and then found the address listed at BofA. I had no other easy way to do it.

Like I said at the end of the first post in this thread "Do what works best for you."

Visit the Bank of America locator at:

http://locators.bankofamerica.com/locator/locator/LocatorAct...

and you will see what I was faced with.

I use excel with ASAP and it is great. With excel you can sort on any column or any combination, ie: sort on state then city.

I agree about the website, I thought you were referring to searching through the spreadsheet.

My point was that there was no list to do as you did.

A better way would be to have all the data in a Data Base and then use a new list to find new and deleted listings. I will probably do that using MS Access, but I'll have to learn how to use it first.

I guess I'll have to go talk to the local branch manager to see if they can help me get an electronic list. That would make the BofA POI so much more manageable.