Excel - What Is The Best Way To
24 March 2009, 01:54 PM
#1
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
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
24 March 2009, 04:11 PM
#5
Scooby Regular
Join Date: Jan 2006
Posts: 897
Likes: 0
Received 0 Likes
on
0 Posts
Trending Topics
24 March 2009, 09:05 PM
#8
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.
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.
24 March 2009, 10:10 PM
#9
Scooby Regular
iTrader: (13)
Join Date: Jan 2001
Location: Midlands - between notts and derby !
Posts: 4,997
Likes: 0
Received 0 Likes
on
0 Posts
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
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
Last edited by DemonDave; 24 March 2009 at 10:12 PM. Reason: added comment
24 March 2009, 11:45 PM
#10
Woah 
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...
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...
24 March 2009, 11:59 PM
#11
Scooby Regular
iTrader: (13)
Join Date: Jan 2001
Location: Midlands - between notts and derby !
Posts: 4,997
Likes: 0
Received 0 Likes
on
0 Posts
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 !
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 !
Thread
Thread Starter
Forum
Replies
Last Post
Mattybr5@MB Developments
Full Cars Breaking For Spares
28
28 December 2015 11:07 PM
Mattybr5@MB Developments
Full Cars Breaking For Spares
12
18 November 2015 07:03 AM
bluebullet29
General Technical
9
05 October 2015 02:17 PM