large files ? 2

[rb1957]

i'm trying to work with a 100M + s/sheet but excel (2003) keeps having nervous break-downs (and not reposnding). i'm about to respond to the screen in an ultimately futile and destructive manner ... is there something i can do, short of splitting the file up ... maybe something with PC settings, memory settings ?

 

[MintJulep]

You might try de-bloating the file.

Start here thread770-80600 and follow the links for various things to try.

 

[rb1957]

i'll look into that ...

is there a "clever" way to see how many times a number occurs in a string (of 41000+ numbers) ? i'm doing it my "dumb" way, which is to look at each number in turn and if it is the number, put 1 in the cell, if not " ", then sum the column.

 

[AELLC]

Can you insert a column (say column B) next to the column of 41000+(say column A, then go B1=IF(A1=123,123,0)

Copy B1 down to row 41000 and sum entire column

 

[MintJulep]

You mean something like =SUMIF()

 

[AELLC]

Sorry, meant B1=IF(A1=123,1,0) where 123 is the number in question.

 

[MintJulep]

Or =COUNTIF() is maybe more in line with what you are doing?

 

[rb1957]

aellc, that's exactly my "dumb" way

 

[cowski]

maybe something with PC settings, memory settings ?

Move to a 64bit computer with plenty of RAM, running the 64bit version of Excel.

is there a "clever" way to see how many times a number occurs in a string (of 41000+ numbers) ?

If you don't mind a little VBA, you can use a regular expression object. It will match a pattern to your string and report the number of matches it finds.

 

[rb1957]

work computer ... so option 1 won't happen untill either ...
a) after i retire,
b) after the default CPU has moved onto a 128bit processor,
c) long after i need it, or
d) after the Eagles retire, really ...

i'll look into the VBA route ... that's where i thought i'd have to go ...

 

[MintJulep]

I'm sure there is a clever way to do what you need to do.

Unfortunately I don't know what it is that you want to do.

Why don't you start a new thread to ask.

Keeping in mind that "strings" and "numbers" and "cells" and "columns" and such have specific meanings in excel.

 

[zelgar]

COUNTIF will count how many times X occurs in a range of data.

If memory is a problem, try the following:
1. Change your windows background to a blank screen.
2. Close all other programs before opening Excel.
3. Turn off Autocalc

Ways of Consolidating your spreadsheet:
1. Look to see if there are cells that can be combined. For example, look and see if there are multiple logic statements that can be combined into one.

2. Also, a number in a cell takes up less space than an equation. You may see significant space savings if you use a macro to do the logic/calculations and pasting the value in the cells than having the equations in all of the cells.

If possible, see about posting a portion of your spreadsheet & we'll try to see what can be done to modify it.

 

[rb1957]

COUNTIF worked a charm ...
reduced the "calc" time from miutes to seconds

 

[dgallup]

Excessive cell formatting can really bloat a spreadsheet. Select all and get rid of borders, fill, fonts, etc.

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

The Help for this program was created in Windows Help format, which depends on a feature that isn't included in this version of Windows.

 

[jmw]

dgallup, may I quote your tagline? And does it have a history?

JMW

http://www.ViscoAnalyser.com

 

[KiwiMace]

Use filters. Filters sort.

 

[dgallup]

JWM

Feel free to quote it. It comes straight from a Windoze 7 error message. If you have any older programs that were created using the standard Windoze (3/95/me/nt/2000/etc.) help system you will get that marvelous error message when you try use help in the old program.

It just strikes me as ironic that Mr. Bill thought it was a good idea to create a proprietary help system, require software companies to use it and then drop support for it in a few years time. Obviously in Redmond every piece of software should be rewritten or replaced on a 3 year cycle of obsolescence.

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

The Help for this program was created in Windows Help format, which depends on a feature that isn't included in this version of Windows.