1

I have some data like this (it's stock market data)

29/07/2015  35972.82    32056.2 17751.39
30/07/2015  35961.86    32046.43    17745.98
31/07/2015  35848.13    31945.08    17689.86
03/08/2015  35662.37    31779.55    17598.2
04/08/2015  35566.11    31693.77    17550.69

There is not one row per day because the data provider omits days in which the market is closed.

I need there to be one row per day, and rows should be duplicated where necessary to fill the gaps

29/07/2015  35972.82    32056.2 17751.39
30/07/2015  35961.86    32046.43    17745.98
31/07/2015  35848.13    31945.08    17689.86
01/08/2015  35848.13    31945.08    17689.86
02/08/2015  35848.13    31945.08    17689.86
03/08/2015  35662.37    31779.55    17598.2
04/08/2015  35566.11    31693.77    17550.69

Is there a way to do this in LibreOffice?

0

Start with the data at the top of a sheet named "Sheet1". Create a new sheet named "Sheet2".

In cell A1 of Sheet2 enter the following formula.

=Sheet1.A1

In cell A2 of Sheet2 enter this formula and fill it down to A7.

=A1+1

For this to work correctly, the dates must be date values, not strings. If the dates on Sheet1 are strings, convert them by going to Data -> Text to Columns. Select the column and specify the date format.

Now in cell B1 of Sheet2 enter the following formula, fill down to B7 then over to D7.

=VLOOKUP($A1;$Sheet1.$A$1:$D$5;COLUMN())

This produces the desired result as shown in the question.

Documentation: VLOOKUP, COLUMN.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.