# Big Database

1. ## Big Database

First of all, I'd like to apologyze for my poor English, but I haven't received any replies on the "Non English Excel forum" so here I am. I'll try my best to explain myself.

So, I need a sheet that contains more than one million rows. It's 10 variations with repetition of 4 elements. That's 4^10= 1.048.576 possible combinations. Also, for each combination I must obtain a probability that depends on the odds given in another sheet (same book). I also need a couple more calculations. I believe you'll see it better in the enclosed file cause I can't explain myself

The thing is I am able to generate all the possible combinations and open them in 2 separate excel sheets, but as soon as I start doing some calculations and dragging down to the bottom of the sheet, excel collapses. Is there any way to do what I want using excel?? I've seen something about Power Pivot but I'm completely lost. Don't even know if it's the right tool for the task. I tried creating a table, but as soon as I did it, all the cells that contained a formula changed the result to #N/A.

Could you please point me in the right direction?? Thanks in advance for your help.

2. ## Re: Big Database

Well if you are saying that Excel collapses after you drag down a formula with the autofill handle over x number of rows then that tells you right there that the formula is either too complex to be calculated automatically like that or your version of excel has a bug in it or there's something wrong with your system. Those would be my guesses. Furthermore if you're running a 32-bit version of Excel or office there is only 65, 632 rows or something like that in there. Because obviously that's the multiple of two that's appropriate for a 32-bit system so if that's what you have then you're going to have to move to Microsoft Access you don't have any other choice or use more than one worksheet

3. ## Re: Big Database

Well, collapsing was a bit of an overstatement. It takes a long time to do it in a sheet with 300k or so rows. Once I save the file and open it again, it takes a lot to open. And if I add more calculations, or change anything, mooore time. And that's only with 300k rows...

By the way, dont know if its relevant but Im running a 64 bit version of Office 365.

4. ## Re: Big Database

VBA points out to make sure you're using 64-bit version of Excel - you might want to consider using the large-format version of Excel, as well... it's the "binary" format, extension is .xlsb.

This file format is approximately twice as fast as .xlsx at opening and saving Excel workbooks. This is particularly useful for very large files (greater than 10MB). This file extension cannot be used by non-Excel applications such as Bloomberg and Capital IQ, so if these systems access your Excel file, then this file format should not be used.

5. ## Re: Big Database

Originally Posted by snkplissken
By the way, dont know if its relevant but Im running a 64 bit version of Office 365.
of course it's relevant. 64-bit gives you more memory slots to fill than 32-bit does. it can handle more transacting the higher the bit count gets.

6. ## Re: Big Database

Didn't know about the binary format. Saved everything in 3 different books and can work with it with a lot of patience. If I change something or want to add more calculations, it's quite painfull though. As you suggested, I might try using Access, but Ive never used it. Do you know of any good tutorial? Is it going to require a lot of time to learn how to do what I want? If I have to invest 50+ hours I might leave it just as it is.

Thanks!

7. ## Re: Big Database

Originally Posted by snkplissken
I might try using Access, but Ive never used it. Do you know of any good tutorial? !
access is extremely simple, but that comes from a mind of a web developer and new-age technologies. for someone who only knows office, it will be a chore to learn it. however, you can make very good use of the people on both of these forums (as I have over the years):

https://www.access-programmers.co.uk/forums/

https://www.utteraccess.com

However, be aware that the people on the first forum above are extremely political and will go to every end possible to preach to you about ""setting up things the right way from the beginning"". they have a very poor understanding of the reality of the business world. the 2nd forum is a little more relaxed in terms of knowing common sense and the chaotic nature of requirements and the changes they go through. however, both forums are political in nature regardless. I think it's just the nature of access developers. every acc. dev I've ever known seems to be heavily political or similar.

8. ## Re: Big Database

I ended up trying Power Bi and it does pretty much what I wanted. Thanks for your help!

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1