At 14:31 15/11/2007 -0800, Mark Knecht wrote:
I have a CSV file with data that changes infrequently. The file
lists only the day the data changes such that there are only 144
lines representing 23 years of data. There are three values for the
data - 1, 0 & -1. The file looks like this:
09/27/74, 1
07/11/75, -1
08/01/75, 1
03/12/76, -1
04/02/76, 1
05/07/76, -1
06/04/76, 1
I need to turn this data into a file with the same format but has
one line of data for every date, something like this:
09/28/74, 1
09/29/74, 1
09/30/74, 1
...
...
...
07/09/75, 1
07/10/75, 1
07/11/75, -1
and so on...
I'm wondering if there is some method of doing this in OOCalc? I'm
not a spreadsheet programmer so I'm hoping for a little guidance.
This fairly straightforward.
o Open your existing CSV file in Calc. (If you are using Windows,
you can do this with right-click | Open With >, but you need to open
it explicitly in Calc, not just in OpenOffice, or it will open in Writer.)
o In the Text Import dialogue, select Comma as the separator.
o Select OK to create a Sheet 1 with your reference table.
o If necessary, go to Insert | Sheet... to insert a Sheet 2.
o In Sheet 2, enter your first date into cell A1.
o Click on the column header of column A to select the column.
o Go to Edit | Fill > | Series... .
o In the Fill Series dialogue, select Down, Date, and Day, fill in
your end date, and set Increment to 1.
o Select OK: you now have a column of your 8401 dates (or thereabouts).
o In cell B1, insert the formula: =VLOOKUP(A1;Sheet1.A$1:B$144;2)
o Select cell B1 and then scroll down to and Shift-click cell B8401
(or whatever) to select the cells in column B corresponding to your dates.
o Go to Edit | Fill > | Down. This should create your required
column of values.
o With Sheet2 displayed, go to File | Save As..., select "Text CSV
(.csv)" for "Save as type", and save the sheet. You will see
warnings that you are saving only values and only the displayed sheet
- but this is what you want.
The VLOOKUP reference searches the first column of the reference
table (the second parameter) for the first parameter: your individual
dates. The third parameter, 2, indicates that it is the value from
the second column of the list that you need as the function
result. Providing that - as here - your reference list dates are
properly sorted, values in between the reference table dates are
given the appropriate value.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
