Speeding up 'Fill Down' function

[colwyn]

I need to fill a formula from G2 down to G60000 but even doing this by copying and pasting, say, 3000 rows at a time, takes forever. As I have many sheets on which to do this - is there a quicker way ??
Thanks.
Colwyn.

[Villeroy]

What is the formula? Turn off automatic calculation, copy down and calculate. Which step is the time consuming one?

[probe1]

I do enter the formula in G2, then enter the range in the named range box (showing G2 by then, changed to G2:G60000), then Edit>Fill>Down, and voila!
Heavy complex formulas may need some (3-6) seconds.

What's your formula?

[colwyn]

Villeroy, when you say "Turn off automatic calculation" - is this via Tools>Options>Oo calc>Calculate and then turn off iterations ???

The formula in G2 is:

{=IF(MAX(ROW(I$1:I1)*(I$1:I1=I2))=0;"";MAX(ROW(I$1:I1)*(I$1:I1=I2)))}

The time-consuming part is when I click 'Fill Down'

------------------------------------------------------------------------------------------

probe1, thanks - that makes a lot of difference. Still taking a lot more than 6 seconds though. Quite a few minutes in fact - but at least I don't have to scroll down 60,000 rows anymore!

Thanks guys.
Colwyn.

[Villeroy]

menu:Tools>CellContents>AutoCalculation
menu:Tools>CellContents>Recalculate [key F9]

Databases are made for what you obviously try to do (getting the latest record of anything). They work performant with large amounts of data (millions of records).

[colwyn]

Villeroy, thanks - I've turned off auto calculate and it's certainly quicker although all hell may be let loose when I turn it back on after filling down the next six sheets! Have filled down 4 columns to row 54728 and am waiting for it to finish - it's been going for about 25 minutes so far. As I'm using OpenOffice 3 beta, I wondered if it would make any difference if I used OpenOffice 2.4?? Or could I perhaps save the file under something other than .odb??

I can see the logic in what you say about databases - but I just can't seem to understand them too well. I would need to input at least 6 sheets of work - each one having columns from A to A and rows 1 to approx 60,000. I've tried pasting data into base but it doesn't work and when I create a table it doesn't let me set formulae. I need to be able to input formulae and later change them should I need to do so.

[Villeroy]

I can fill entire unformatted columns with simple formulas (=ROW(), =RAND()) within a second.
If you are not able to paste your data into a database this may indicate that your data are inconsistent already. For instance, you may have string values and numeric values mised in the same column, or you try to fill a column of small integers with at least one large integer, or you try to import a field as primary key although it has duplicates. In a spreadsheet there is no mechanism to prevent inconsitancies like this. In a spreadsheet you need some non-trivial formulas which constantly alert for duplicates and "wrong" values. These formulas decrease performance even further. Cell validation is not the way to go. You can clear (Del, Backspace), paste, drag&drop, insert, remove cells at any time. Spreadsheets are mainly for calculating numbers by position (relative references) while beeing very poor list keepers.

[jrkrideau]

Villeroy, thanks - I've turned off auto calculate and it's certainly quicker although all hell may be let loose when I turn it back on after filling down the next six sheets! Have filled down 4 columns to row 54728 and am waiting for it to finish - it's been going for about 25 minutes so far. As I'm using OpenOffice 3 beta, I wondered if it would make any difference if I used OpenOffice 2.4?? Or could I perhaps save the file under something other than .odb??

I can see the logic in what you say about databases - but I just can't seem to understand them too well. I would need to input at least 6 sheets of work - each one having columns from A to A and rows 1 to approx 60,000. I've tried pasting data into base but it doesn't work and when I create a table it doesn't let me set formulae. I need to be able to input formulae and later change them should I need to do so.

If I've understood this correctly you have something like 1 million cells? Is this mainly data or a mix of data and formulae?

If it's mainly data upon which you then need to perform some operations I'd strongly support Villroy's suggestion of a data base however I'd also suggest that a good statistics program ( R (Opensourece),SPSS,(not to expensive) , SAS ( exhorbitant) may be alternatives. I don't think a spreadsheet is really designed to do this type of work. It can but it's just the wrong tool.

[colwyn]

Rideaujrk, yes it involves both text and numbers. It is historical data covering past years and spans columns A-AG,rows 1-60,000 (approx) and each sheet covers one year. It can only function through formulae. It is only personal not business and I've no real knowledge of database. My spreadsheet seems to be causing my computer to sieze up and I'm just trying to find ways around this - if possible.
Colwyn.

[Villeroy]

You have one sheet per year. How can you query records across years? In a database every sheet would be a table, and there are methods to merge tables permanently and temporarily.
OK, let's say you can afford a legal copy of the latest Excel (which allows for many more rows and should be faster) and merge the year-sheets into one. There may be still inconsistencies in the merged table which prevent reliable queries. All spreadsheets are extremely tolerant by design. Every single cell is allowed to take any possible value.
The formula you mentioned indicates a certain table design where the order of rows matters. This could be another reason why your data are difficult to process by any generic tool. Are your data collected from human readable sources such as websites?

If you think that performance is your main problem and Excel is not an option, try Gnumeric from http://gnumeric.org. It's my favourite spreadsheet application.

Rideaujrk, yes it involves both text and numbers.

The important question is: Does it involve both text and numbers within the same field? This would be the first problem to be solved.

[colwyn]

Villeroy, I've downloaded gnumeric and input the formula =sum(E9+E10) and the cell shows the formula and not the result ????


You say: "The important question is: Does it involve both text and numbers within the same field? This would be the first problem to be solved."


Whilst text and numbers feature across the spreadsheet, individual columns contain ONLY text OR numbers
Is this helpful?
Colwyn.

[Villeroy]

Villeroy, I've downloaded gnumeric and input the formula =sum(E9+E10) and the cell shows the formula and not the result ????


You say: "The important question is: Does it involve both text and numbers within the same field? This would be the first problem to be solved."


Whilst text and numbers feature across the spreadsheet, individual columns contain ONLY text OR numbers
Is this helpful?
Colwyn.

Like other spreadsheets Gnumeric has an option to show all formulas: menu:Format>Sheet>Show Formulas [Ctrl +].

I believe it's English version uses comma as list separator rather than Calc's semicolon. My German Gnumeric uses semicolon since we use comma as decimal separator. Gnumeric can import ODF to some extent. Just load your Calc file and see.
Yes, database-like lists need to be consistent in order to filter, aggregate and sort correctly.
I have written a Calc add-on for this purpose among many others: http://ooomacros.org/user.php#221020
Gnumeric has something similar in it's find&replace tool.

[jrkrideau]

Villeroy, I've downloaded gnumeric and input the formula =sum(E9+E10) and the cell shows the formula and not the result ????


You say: "The important question is: Does it involve both text and numbers within the same field? This would be the first problem to be solved."


Whilst text and numbers feature across the spreadsheet, individual columns contain ONLY text OR numbers
Is this helpful?
Colwyn.

Another question is what do you call text? To me text is a set of words. lets say the column contains a list of names

• James
  Tin
  Harry
  Sue
  Abdullah
  Eric

This to be is text. A column with formulae would be something like

• SUM(D1;D4)
  SUM(D1;D4)
  MOD(C1;1)
  MOD(C1;1)

Which do you have?

[colwyn]

Rideaujrk,thanks, I have columns whose cells contain only text - columns whose cells contain only numbers - and columns whose cells contain formulas that produce text in those columns - and columns whose cells contain formulas that produce numbers in those columns.

Another problem I've hit is that when I try to fill down formulas the computer freezes and I have to reboot. This happens in Excel, Openoffice and Gnumeric.
Colwyn.

[jrkrideau]

Rideaujrk,thanks, I have columns whose cells contain only text - columns whose cells contain only numbers - and columns whose cells contain formulas that produce text in those columns - and columns whose cells contain formulas that produce numbers in those columns.

Okay, got it. The more I hear about this data, etc, the more I am inclined to think that you really need to switch to a data base or a stats package. That way you can split the data from the operations and reduce the size of your problem. Operations that require equations in each row of a column to match the data in another column can usually be replaced with a one-line command.

For example if I wanted to do the equivalent of a fill-down command to fill a complete column with random numbers in R (the stats program I normally use) I would need a one-line command { aa <- rnorm(65,536)} and to multiple the equivalent of the column by 2 becomes {2*aa} . Data bases offer the same kind of thing

There is definately a learning curve for something like this but give the large amounts of data and formulae you're dealing with, I suspect that you'll save a lot of time in the long run.

What exactly are you doing with this data etc? The final output may help us suggest other approaches to what you are currently trying to do.

Another problem I've hit is that when I try to fill down formulas the computer freezes and I have to reboot. This happens in Excel, Openoffice and Gnumeric.
Colwyn.

I'm just guessing here but is it possible that you're running into a physical memory problem here?

I don't think the same problem should be showing up in all three spreadsheets as a software problem

[colwyn]

I also wondered if it was a hardware problem. I have 1gb ram and 80gb hard drive with AMD 2600+ processor. Thought this should have been sufficient.

[jrkrideau]

I also wondered if it was a hardware problem. I have 1gb ram and 80gb hard drive with AMD 2600+ processor. Thought this should have been sufficient.

Well 1 GB sounds like a lot but who knows? You might try making sure that nothing is running but OOo and see what happens.