Help with Advanced Functions in Open Office?

Question

Unfortunately, neither I nor anyone that I work with is knowledgeable about spreadsheets. The question I am going to ask is rather complex. So I will do the best I can to explain what I am trying to achieve.

Basically we are building a directory within our site, that will list various businesses based upon their service. This information is searchable by zip code.

Initially we are going to do a mass import of businesses into our site, and use the information to help build our network.

Our goal is to have about 10 listings to work with per category, per zip code. Now some categories have larger amounts of businesses whereas others have very few. The range of these business lists/CSV's is anywhere from 65 to 1500 businesses. We have about 90 categories, and about 100 zip codes we are targeting.

What have have done is define specific zones within our site. These zones are made up of numerous zip codes. We now have about 23 individual zones.

The way our site works is it references a CSV which defines what zip codes belong to what zones.

for instance:

Berea OH 44017 OH1

* Here you see the city, state, zip code, and OH1 which is the assigned zone. It follows this format through all zip codes we are targeting.

The business CSV's follow this format in individual columns as well

Name of Business, Street Address, City, State, Zip, Phone, Service

* Each service is a separate CSV.

So what will happen is my partner will run an import which will loop through, as assign a business to a zone based upon what zone that businesses zip code falls into.

Here are the problems and why I need help with function on spreadsheet.

- First, when the import runs on some categories/services we just won't have enough businesses to hit my intended goal of 10 businesses per zip per service. Why? because geographically some zones carry more businesses than others.

It is extremely important that we do carry at least 10 businesses per page.

What I would like to be able to do is open a service CSV, and have a glance view of what zone each business falls into, sort that information by zone, and see if I have hit my intended goal, and then manually assign more business to any zone that needs more.


The problem is the service CSV only list the business by zip code.


So what I was hoping to do was copy the zip code column within each of these services CSV, and have a way to quickly convert the copied column of zip codes into a zone. Basically that the CSV knows hey 44017 within this column really means OH1, and it does so all the way down the list.

I have no Idea how to achieve this, or if it is even possible, but if If anyone could help it would save me months of manually changing thousands of listings.

Remember each CSV is individualized based upon service. So as soon as I get one done i am moving to the next.

If there is a fix, please be descriptive because I am not a coder. So i'm hoping this is really an easy request, and just difficult to the layman like me.

Thank you.

Answer 1

well there are lots of answers and lots of possible things to try.

it seems you have a lot of separate CSV's

including one with the data in it that you require, ie ZIP vs AREA

in some dimension or other.



lets make a few statements

1 you need a coder really, and im not a web editor

2 HTML pages also contain JAVASCRIPT and PHP and lots of other languages all mingled in.

so you can kind of flip between them all on one page although it makes sense to develop them separately.

3 because things like PHP already have pre-programmed functions to LOAD the contents of a CSV file, they are quick and easy to use.



so thinking about the above, it would be reasonably straight forward to alter a system that had say 23 separate webpages(one for each area) into just ONE webpage, that could select the Area from some sort of MAP or LIST, or business address, and just LOAD the CSV/CSV's needed.



so the whole website is just ONE page.



this is the beauty of PHP, its dynamic(using php to do this is just an example).



you dont get to SEE the page until it has WORKED or completed its LOAD.



you can see some Random examples offered up here at the PHP home site, for the function FGetCSV



http://php.net/manual/en/function.fgetcsv.php



each example is quite short code, maybe half a page.



now some examples of what you could program.



you could

1 create a PHP page that opened a CSV, and a CSV of area codes vs ZIP, and then APPEND a COLUMN to the data containing what you want, and then WRITE a new CSV with a new name eg Customer97.CSV --> Customer97withAreaCode.CSV

2 you could then make this code LOOP through ALL the CSV files, NAMING and CREATING output files with the new column appended.



so to sum up

your data is in CSV files

its easy to read CSV files using PHP

its easy to add columns of data, and re-ouput to a new csv

its easy to repeat the process for as many CSV's as you have got.

its easy to delete all the output files if you make a mistake.



get a coder

Answer 2

All of your target goals are descriptive of gleaning a set using relationships. This is the definition of database. PHP is a scripting language that bridges a database to a web page result. The bonus with that set-up is you are ready to network.



OpenOffice -- the free office suite that is a drop-in for MSOffice does have programming capabilities but then it becomes complex to network the workstations together. MSOffice from Microsoft has similar capabilities that one would purchase as an add-on feature.



Link below is the closest how-to book if you wanted to DIY. PHP is easy to get into but takes a lifetime to master. 'Head First PHP and MySql' shows actual examples. PHP, itself, is capable of reading .CSV files. You still need the MySql database to gain the SQL programming language that gleans the specific records you seek from the vast data store you have.

Answer 3

=IF (A1=1, "text1", IF(A1=2, "text2", IF(A1=3, "text3",""))) That says if A1=1 then return "text1", but if it's 2 then return "text2", but if its 3 then return "text3", otherwise leave it blank.