On Nov 15, 2007 6:28 PM, Brian Barker <[EMAIL PROTECTED]> wrote: > > 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
Thanks Brian. It's seems to have worked very well and probably took me less time to actually do than you spent writing up the instructions. I really appreciate your efforts! Cheers, Mark --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
