Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,690
    Thanks
    59
    Thanked 88 Times in 82 Posts

    Large number of formulas cause recalc bottleneck...is there a better way?

    I have a large sheet (15,000+ rows by 82 columns). The cells each contain a formula that is a SUMIFS as noted below.
    It's an awful recalc. I've been scratching my head to see if there's a better approach to this SUMIFS that'll be considerably faster.

    Any thoughts?

    Code:
    =SUMIFS(SRBO!$F$2:$F$15767,SRBO!$AR$2:$AR$15767,$A2,SRBO!$C$2:$C$15767,C$1)+SUMIFS(SRBO!$J$2:$J$15767,SRBO!$AR$2:$AR$15767,$A2,SRBO!$G$2:$G$15767,C$1)+SUMIFS(SRBO!$N$2:$N$15767,SRBO!$AR$2:$AR$15767,$A2,SRBO!$K$2:$K$15767,C$1)+SUMIFS(SRBO!$R$2:$R$15767,SRBO!$AR$2:$AR$15767,$A2,SRBO!$O$2:$O$15767,C$1)+SUMIFS(SRBO!$V$2:$V$15767,SRBO!$AR$2:$AR$15767,$A2,SRBO!$S$2:$S$15767,C$1)+SUMIFS(SRBO!$Z$2:$Z$15767,SRBO!$AR$2:$AR$15767,$A2,SRBO!$W$2:$W$15767,C$1)
    More elaboration on what's being done:

    SRBO, column AR has customer names. The sheet in column A has customer names. So, I need to find customer by customer.
    Then, I need totals for each customer by adding the data in columns F, J, etc. if their product names in C, G, etc. [respectively] match the product headings in row 1. [make any sense?]
    Last edited by kweaver; 2018-08-10 at 16:34.

  2. #2
    WS Lounge VIP Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    1,272
    Thanks
    291
    Thanked 197 Times in 170 Posts
    Sounds like it would go much faster with Access DB used to store the info, feeding into Excel workbooks as needed.
    Lugh.
    ~
    Dell Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
    i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 2 x 256G SSD, 4TB HD

  3. #3
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,690
    Thanks
    59
    Thanked 88 Times in 82 Posts
    Lugh, thanks...that's the conclusion I came up w/today as well...I think I'll have to investigate that move. Nice to have some agreement.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,318
    Thanks
    164
    Thanked 624 Times in 593 Posts
    Hi Kevin

    Why use scary SUMIFS when you could just do a quick convert to a pivot table?
    With a little bit of vba you could automate the whole process.
    In my test with 16,000+ records, it takes a blink of an eye.

    See may attached file

    First of all, save the attached file to a test folder of your choice.
    Then rename the file extension from .pdf to .xlsb
    Ignore the warning about changing file extension.
    This is because the attached 2MB file is really an Excel file, NOT a pdf file.
    In this Lounge, you can post larger files if they are "of pdf type" (you might have seen me use this trick to bypass the file-size limits here).

    So, open the attached file in Excel, and you'll see it has a single sheet named [SRBO] with what I believe is a similar layout to your data.
    Click the button [Product Sales by Customer] and the vba code will create new sheets [pivotData] and [pivot].

    Voilla!

    Is this something that you could use???

    zeddy
    Excel Combat Laundry Sheet Services
    Attached Files Attached Files

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,318
    Thanks
    164
    Thanked 624 Times in 593 Posts
    Hi Kevin

    So, if my file does the trick, just paste your [SRBO] sheet into the file and click the button.
    etc etc etc.

    zeddy

  6. The Following User Says Thank You to zeddy For This Useful Post:

    kweaver (2018-08-11)

  7. #6
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,690
    Thanks
    59
    Thanked 88 Times in 82 Posts
    GREAT IDEA, Zeddy.

    With some minor adjustments at my end, this works just great!

    I need another version of this as well, and think I can modify this accordingly for the 2nd approach.

    YAY!

  8. #7
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,690
    Thanks
    59
    Thanked 88 Times in 82 Posts
    What's the downside, if any, of saving a workbook as an XLSB? If not downside, why not do this by default?

  9. #8
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    9,051
    Thanks
    65
    Thanked 1,144 Times in 1,065 Posts
    XLSB is binary format instead of text and can be more efficient for large files.
    https://stackoverflow.com/questions/...ormats-be-used

    cheers, Paul

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,318
    Thanks
    164
    Thanked 624 Times in 593 Posts
    Hi Kevin

    Paul has provided a great link discussing the .xlsb format.
    This is my file format of choice, because the file-size is smaller and you can also have macros in the file.
    Smaller file-sizes means network traffic is less overloaded, smaller files load faster etc etc etc.
    A minor downside (in my opinion) is that if your file gets corrupted, it is tougher to forensically recover useful data from a binary file. But then you would have a better backup policy than relying on forensic data recovery!

    zeddy

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,318
    Thanks
    164
    Thanked 624 Times in 593 Posts
    Hi Kevin

    So, you liked my method then!
    And the .pdf attachment trick worked.

    For the benefit of others, I used your SUMIFS formula in post#1 to work out
    columns C, G, K, O, S, W had Product Type info
    columns F, J, N, R, V, Z have Amounts

    Re: I need another version of this as well
    Perhaps your data has Qty columns as well as Amounts, and you need to check number of Product units shipped to Customers. I'm guessing here, but, assuming this is the case, the following attached file will also generate another sheet [SalesByQty] with analysis of Product Qty per Customer.

    I previously assumed that there would be "no gaps" in the data records so I used the end(xlup) method in each column to append the data. This is probably not a good idea.
    So I generated some new test data records that had data gaps within the Customer record rows.
    The processing now takes this into account.

    I also added a couple of coding tweaks to change numberformats, freezing the window headings etc etc etc.

    So, as before, save the attached file to a folder of your choice, and rename the file from .pdf to .xlsb.
    Is this version any better?
    Is my dummy data good enough?

    zeddy
    Excel Prophetic Layout Estimators
    Attached Files Attached Files

  12. The Following User Says Thank You to zeddy For This Useful Post:

    kweaver (2018-08-12)

  13. #11
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,690
    Thanks
    59
    Thanked 88 Times in 82 Posts
    Your sample file(s) were PERFECT and allowed me to accomplish what I needed! GREAT PDF trick as well!

    You're work is amazing and greatly appreciated.

    I don't think the wine I'd send you would travel well across the pond.

    EDIT: Here's part of an adjustment I need but cannot tell where I need to adjust the macro.
    Suppose I now want another column on the "pivot" output (suppose it's to be column B and the other shift over 1 to allow for this.
    In that column, which I want to be able to cherry pick from a drop-down, it's "source" which comes from SRBC, column B. I stumbled on a few tries of my own, but they didn't work.
    Last edited by kweaver; 2018-08-12 at 11:21.

  14. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,318
    Thanks
    164
    Thanked 624 Times in 593 Posts
    Hi Kevin

    If I ever get to La Jolla I would very much look forward to drinking that wine!

    Now, just to be clear, are you wanting to a have a pivot table 'page' field?
    For example, if your [SRBO] sheet has a Source column B (let's say, US State data), you want to include this column in the pivot data source block, and then want to select the Source from a Page-field dropdown in the pivot table, and see the corresponding data in the pivot table?

    zeddy
    Excel Threat Identification Unit Leader

  15. #13
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,690
    Thanks
    59
    Thanked 88 Times in 82 Posts
    Yes, you stated it correctly. (I renamed SRBO to SRBC, but that's no issue).
    The B column in SRBC has the "source" in it (I might want to add 2 or 3, but i suspect if I know how to add 1, I can add 3).
    The product names are currently in C, G, K, etc. and the totals are in F, J, N, etc.
    AR still has the customer name in it.


    P.S. Where will ALWAYS be a great wine available whenever you make it here (or even close...I'll drive to meet up with you).

    /K

  16. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,318
    Thanks
    164
    Thanked 624 Times in 593 Posts
    Hi Kevin

    OK, give me a moment to update the code, run a test or two, and post back an updated sample file for you.

    zeddy

  17. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,318
    Thanks
    164
    Thanked 624 Times in 593 Posts
    Hi Kevin

    ..here's the v3 with a Pivot Page field for selecting Source.

    Let me know if this does what you are looking for.
    You can search the vba code for v3 to see the changes I made etc etc etc

    zeddy
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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