×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

large files ?
2

large files ?

large files ?

(OP)
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 ?

RE: large files ?

(OP)
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.

RE: large files ?

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  

RE: large files ?

You mean something like =SUMIF()

RE: large files ?

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

RE: large files ?

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

RE: large files ?

(OP)
aellc, that's exactly my "dumb" way

RE: large files ?

Quote:

maybe something with PC settings, memory settings ?
Move to a 64bit computer with plenty of RAM, running the 64bit version of Excel.

Quote:

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.

RE: large files ?

(OP)
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 ...

RE: large files ?

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.

RE: large files ?

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.

RE: large files ?

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

RE: large files ?

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.
 

RE: large files ?

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

JMW
www.ViscoAnalyser.com

 

RE: large files ?

Use filters.  Filters sort.

RE: large files ?

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.
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources