creating a new dataset
 

I'm reworking a dataset in an excel worksheet and am wondering if anyone
knows the best way to do this.

This is the structure of my original dataset, though the whole dataset
is much larger:

number of sales:

country city product 1950 1955 1960 1965
Australia Sydney cars 125 800 754 222
USA New York sneakers 7500 768 555 369
Mexico Cancun condos 56 230 33 679
France Paris shoes 87 99 56 88

The whole dataset is 7 columns across (columns A-G) and the line with
the field headings is on the fourth row of the worksheet.

I would like to rework it so that:

- the computer goes through each of the cells in the dataset
- if the cell contains a number of sales value which is between 100 and
600, then create the following line based on that cell: country, city,
product, year, value
- create a new worksheet, put a line with the field headings, and put
the list of all these lines in this worksheet

For example, if this were to run on the above dataset, the new dataset
created in the new sheet would be:

country city product year sales value
Australia Sydney cars 1950 125
Australia Sydney cars 1965 222
USA New York sneakers 1960 555
USA New York sneakers 1965 369
Mexico Cancun condos 1955 230

Also, based on the above new dataset, does anyone have any suggestions
on how to do the following:

- Go through each line and look at the value for year.
- If year does not equal 1950, 1960 or 1964, then extract the whole line
and put it in a new worksheet. Change the year value in the dataline in
the new worksheet to the year to which it is closest among the following
years (1950, 1960, 1964). Add a new column to the end of the dataline
with field heading "note" which says the following, "Data for" X (year
to which the original value was changed "refers to" X (original year
value)
- Do this for all lines for which the year value is not 1950, 1960 or
1964.

For example, if this were to run on the above data set, the dataset
generated in the new worksheet would be:

country city product year sales note
Australia Sydney cars 1964 222 Data for 1964
refers to 1965
USA New York sneakers 1964 369 Data for 1964
refers to 1965
Mexico Cancun condos 1950 230 Data for 1950
refers to 1955


Space was a little crunched on the above dataset, but hope you got a
sense.

Thank you, and looking forward to your suggestions.

Best regards,

Brian Kaufmann





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!