Ask Your Question
0

How to insert empty cells between non-empty cells in the entire column? [closed]

asked 2012-11-29 21:12:59 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Hello,

I am trying to add up data from multiple excel-files to make a new one with all the data in one file. The data consists of observation data taken on time intervals. In one file data is taken every 30 minutes and in another file data is taken every 60 minutes. When I group the data from both files the dates don't match, because of different sampling rates.

So for example I have the following kind of data in excel-file 1:

    1/1/2006     15:00     100
    1/1/2006     15:30     97
    1/1/2006     16:00     105
    1/1/2006     16:30     101
    1/1/2006     17:00     97
    .
    .

etc

and in excel-file 2 I have:

1/1/2006     15:00     3
1/1/2006     16:00     5
1/1/2006     17:00     4
.
.
etc

Now if I add up these files I get the following:

1/1/2006     15:00     100     3
1/1/2006     15:30     97      5
1/1/2006     16:00     105     4
1/1/2006     16:30     101
1/1/2006     17:00     97

But as you can see this is wrong, because of the different sampling rates

What I would want is the following:

1/1/2006     15:00     100     3
1/1/2006     15:30     97      
1/1/2006     16:00     105     5
1/1/2006     16:30     101
1/1/2006     17:00     97      4

So what I need to do is add extra cells between the values in the column from excel-file 2. I hope I was clear enough =) How can I do this in Libreoffice Calc, because inserting cells manually is not an option because I have more than 10 000 rows of data =)

all help appreciated! =)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-19 02:32:39.720012

2 Answers

Sort by » oldest newest most voted
0

answered 2012-11-30 09:14:14 +0200

LazyKsaw gravatar image

You could use a macro to manually insert cells 10,000 times for you. I recorded a macro spacing a couple cells manually, and then tweeked it to run relative to the cell selected, where ever that may be, and work down. You can edit the number in the for/next loop to your number of rows, I only tested it to 160.

First, copy the excel2 sheet into a LibreOffice.Calc document so you can't damage the original, of course. Then use the following code to make a macro. When it's ready, begin by selecting the cell at the top of the column of cells you want to space out, and run the macro. Presto! You can run it again on the other columns also, if you need to.

sub SpacerMacro
rem -- This macro will Insert/ShiftCellsDown to space out a column of cells 
rem -- Run the macro beginning with the first cell of the column selected.
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim args1 as integer
dim args2(0) as new com.sun.star.beans.PropertyValue
dim args3(1) as new com.sun.star.beans.PropertyValue

args2(0).Name = "Flags"
args2(0).Value = "V"

args3(0).Name = "By"
args3(0).Value = 1
args3(1).Name = "Sel"
args3(1).Value = false

rem --- Need to shift down one cell initially before inserting
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args3())

rem --- After that, it needs to shift down 2 cells each time, 
rem --- the one it just inserted and the the next non-empty one.
args3(0).Value = 2

rem ---- CHANGE THE "to " NUMBER TO SUIT YOUR NUMBER OF ROWS TO SPACE
for args1= 1 to 160
dispatcher.executeDispatch(document, ".uno:InsertCell", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args3())
next args1

end sub

Easy peasy, right? I hope...

edit flag offensive delete link more
0

answered 2012-11-30 05:43:01 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Can you not make the cells in row D double height

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2012-11-29 21:12:59 +0200

Seen: 1,145 times

Last updated: Nov 30 '12