+ Reply to Thread
Results 1 to 8 of 8

Big Database

  1. #1
    Registered User
    Join Date
    11-21-2020
    Location
    Bilbao
    MS-Off Ver
    365
    Posts
    5

    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.
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    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. #3
    Registered User
    Join Date
    11-21-2020
    Location
    Bilbao
    MS-Off Ver
    365
    Posts
    5

    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. #4
    Forum Contributor
    Join Date
    07-19-2019
    Location
    Illinois, USA
    MS-Off Ver
    365
    Posts
    164

    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. #5
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Big Database

    Quote Originally Posted by snkplissken View Post
    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. #6
    Registered User
    Join Date
    11-21-2020
    Location
    Bilbao
    MS-Off Ver
    365
    Posts
    5

    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. #7
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Big Database

    Quote Originally Posted by snkplissken View Post
    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. #8
    Registered User
    Join Date
    11-21-2020
    Location
    Bilbao
    MS-Off Ver
    365
    Posts
    5

    Re: Big Database

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 10
    Last Post: 03-12-2019, 01:04 AM
  2. Replies: 2
    Last Post: 10-22-2014, 11:32 PM
  3. Need macros to extract data from database/compare/update database - Excel 2007
    By saroby in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-15-2014, 01:45 PM
  4. Match data between CSV and database and update column in database table
    By djfscouse in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-05-2012, 05:59 PM
  5. Search Through Database And Display Results In Userform without showing database
    By nirvanarapeme in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-04-2012, 05:18 PM
  6. Replies: 5
    Last Post: 06-11-2006, 04:45 PM
  7. Replies: 2
    Last Post: 03-08-2006, 04:45 PM

Bookmarks

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