Excel - What Is The Best Way To
I have a database of some 450,000 records consisting of about 10 fields per record - mix of text/number and date fields
Using Excel 2007, I can potentially put all these records on one sheet, which makes the formulas simpler of course but is not essential. On the record sheet I run a number of calculations (15 or so) and then use a front sheet to display grouped results based on IF statements (countifs/averageifs etc) All pretty simple stuff but the problem is that the size of the spreadsheet grows enormously! Currently 45mb. What tips/tricks to keeping the size as small as possible? Should I use external links (other workbooks) or maybe an access db (the data is a result of a complex sql query otherwise I could reference the sql db directly). Keep it on 1 sheet? Anything? Cheers!! Puff Puff |
Simple thing you've probably looked at - is "track changes" off? Used to cause a lot of file size issues.
|
Its off :D
(Had never thought to look lol) |
I would go to at least an access DB if not SQL server. It would depend on how business critical it is !
|
Originally Posted by DemonDave
(Post 8597898)
I would go to at least an access DB if not SQL server. It would depend on how business critical it is !
Access as a minimum :thumb: Wouldn't use Excel for anything over a few thousand rows myself. Can you not use the Source SQL DB itself? |
excel sucks for that sort of stuff, get an access db, much faster and smaller!
|
SQL is the way forward, access sux to!
|
OK - going to try Access.
So what is =IF(A1="","",IF(A1=DATE(1900,1,0),"",IF(B1=DATE(19 00,1,0),A1,IF(B1>A1,B1,B1)))) In Access? Two date/time columns which may or may not be blank & I'm asking if B1 exists, if its before A1 put A1 and if it exists and is after A1, put B1. |
SELECT
IIF (DATEDIFF("D", IIF(IsDate([DATE-A]),[DATE-A],"01/01/1000") , IIF(IsDate([DATE-B]),[DATE-B],"01/01/1000") ) <= 0 , [DATE-A] , [DATE-B]) AS [RETURNED-DATE] FROM [DATA-TABLE]; Will work from year 1000 upwards only :) |
Woah :eek:
I feel a whole new learning curve coming on here! I knew there was a reason I stuck to Excel... If this was stuck in column C in the db would it work it out for all the records individually? I presume this is run as a query somehow.. Gah - head time... |
Just run that as a query along with the other columns you want to return ...
if you create a table in access and let me have the columns names it will be v easy to recreate the query Give us a shout if you want some help ! |
YHM!
|
All times are GMT +1. The time now is 05:28 AM. |
© 2024 MH Sub I, LLC dba Internet Brands