finding non-standard years in a dataset
 

Hi,

I had sent this a while back and am wondering if you might have some
ideas on this.

I'd like to be able to do the following on this excel dataset:

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

- 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.

Thanks, your suggestions would be much appreciated.

Best regards,

Brian Kaufmann





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

finding non-standard years in a dataset
 

Im working on the code now. Tonight it will be finished i think

regards
max
"brian kaufmann" wrote in message
...
Hi,

I had sent this a while back and am wondering if you might have some
ideas on this.

I'd like to be able to do the following on this excel dataset:

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

- 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.

Thanks, your suggestions would be much appreciated.

Best regards,

Brian Kaufmann





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

finding non-standard years in a dataset
 

Hi Brian Kaufmann,

I think I made a nice little program to do the stuff you want. If there is a
messagebox which says enable or disable macros at the start up of your
workbook, click enable.

In the workbook you will see a button, called "Check". This will go through
your data untill row 2000, and does all the things you wanted and puts it in
sheet2, called New.

Hope this helps and please notify me if this is what you are looking for.

Regards
Max Potters
The Netherlands


"brian kaufmann" wrote in message
...
Hi,

I had sent this a while back and am wondering if you might have some
ideas on this.

I'd like to be able to do the following on this excel dataset:

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

- 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.

Thanks, your suggestions would be much appreciated.

Best regards,

Brian Kaufmann





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

finding non-standard years in a dataset
 

I have sent the attachment to your mail address
"Max Potters" wrote in message
...
Hi Brian Kaufmann,

I think I made a nice little program to do the stuff you want. If there is
a
messagebox which says enable or disable macros at the start up of your
workbook, click enable.

In the workbook you will see a button, called "Check". This will go
through
your data untill row 2000, and does all the things you wanted and puts it
in
sheet2, called New.

Hope this helps and please notify me if this is what you are looking for.

Regards
Max Potters
The Netherlands


"brian kaufmann" wrote in message
...
Hi,

I had sent this a while back and am wondering if you might have some
ideas on this.

I'd like to be able to do the following on this excel dataset:

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

- 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.

Thanks, your suggestions would be much appreciated.

Best regards,

Brian Kaufmann





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