+ Reply to Thread
Results 1 to 33 of 33

=IF and =SUMIF formulas creating long long long data processing times.

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    U.S.
    MS-Off Ver
    2013
    Posts
    66

    =IF and =SUMIF formulas creating long long long data processing times.

    Ok, here we go. The attached file has a fix for a complex number sorting and adding problem.

    The sheet is does the requisite calculations. this takes FOREVER with a fast CPU and the file on an SSD..

    It was suggested in another thread that VBA might be able to speed this up....
    Last edited by comp; 03-03-2014 at 12:42 PM.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    Hi Comp,

    I suspect that I've oversimplified your task - so let me know what I didn't include:

    Please Login or Register  to view this content.
    Just use Columns A:D and the output will show in column E.

    Directions for running the routine(s) just supplied

    Copy the code to the clipboard

    Open your Workbook

    Save your Workbook or Another Workbook designated for Macros as Macro_Enabled

    Press ALT + F11 to open the Visual Basic Editor.

    Select “Module” from the Insert menu

    Type "Option Explicit" then paste the code under it

    With the cursor between Sub and End Sub press F5 (F8 to Single Step)

    OR

    Press ALT + Q to close the code window.

    Press ALT + F8 then double click on the macro name
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    02-06-2013
    Location
    U.S.
    MS-Off Ver
    2013
    Posts
    66

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    The string of values has this same repeating sequence of 8 outputs that need added, but the outputs continue and continue one after the other..

    After each series of 8 are added, another series of 8 begins, but the ending point of each string can be different because of the fluctuating string lengths. This continues on all the way down the page.

    The formula needs to mirror the method used in the first post, whereas it determines the starting numbers and uses those to make the calculations.

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    Perhaps a little larger sample is needed because I've replicated the H column results with my little program, but I'll keep pondering

  5. #5
    Registered User
    Join Date
    02-06-2013
    Location
    U.S.
    MS-Off Ver
    2013
    Posts
    66

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    Sorry, here is a longer output, an actual output. If you paste the values in A, B, C, and D into the first document, and copy down the E and F columns all the way down, to help determine the start of each string of data. Thanks for your help, just running that small section of it was much faster than a comparable section using my current method!
    Last edited by comp; 02-25-2014 at 07:38 PM.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    Maybe:

    Please Login or Register  to view this content.

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    Hi Comp,

    I got:

    Nevermind

    Thanks for the rep
    Last edited by xladept; 02-25-2014 at 07:48 PM.

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    Here's the latest (it may be working now):

    Please Login or Register  to view this content.
    *But it gives 329 not 8??

  9. #9
    Registered User
    Join Date
    02-06-2013
    Location
    U.S.
    MS-Off Ver
    2013
    Posts
    66

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    I think the hardest part is describing the function that i need. Here is a thread that explains it .... If you read this, and then the other thread i Linked at the end, you will see the progression that explains this problem. the thing is, the forumlas im using work very well, i just need the same thing put into a macro essentially,



    how-to-list-cell-location-dependent-upon-another-cells-value

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    Can you post something with the expected results? I might then be able to understand.

  11. #11
    Registered User
    Join Date
    02-06-2013
    Location
    U.S.
    MS-Off Ver
    2013
    Posts
    66

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    Example1.xlsx Sure, here you go. The formulas work perfectly, they just need ported to MAcro form, if that is possible.
    Last edited by comp; 02-26-2014 at 10:16 AM.

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    OK - you posted the first example again and I'm more confused than ever - my last routine
    Comp3 - produces those results, my second routine Comp2 - produces those results and my first routine Comp also produces those results????

    Can you please articulate the problem with Comp3 - and are the results always 9 numbers???

  13. #13
    Registered User
    Join Date
    02-06-2013
    Location
    U.S.
    MS-Off Ver
    2013
    Posts
    66

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    No they are not always 9 numbers. there are 8 strings of data, listed sequentially. These strings of data need combined.
    Each string can be of varying length. Once they reach the end (usually around 300 times) another set of 8 begins. This can be hundreds, thousands, of values in the end.
    PHP Code: 
    1. 2
    2. 3
    3. 4
    4. 4
    1. 2
    2. 3
    3. 4
    4. 4
    1. 2
    2. 3
    3. 4
    4. 4
    1. 2
    2. 3
    3. 4
    4. 4
    1. 2
    2. 3
    3. 4
    4. 4
    1. 2
    2. 3
    3. 4
    4. 4
    1. 2
    2. 3
    3. 4
    4. 4
    1. 2
    2. 3
    3. 4
    4. 4 
    these would end up with 1 = 16, 2=24, 4=32, etc.

    Once this iteration is done, another begins. another set of numbers below it. that ALSO need combined, separately, as a separate line of 8 strings. This continues repetitively, forever.


    The included formulas in the two cells work correctly, they just need ported to VBA if possible. Is there a way to use the same formulas in VBA?
    Last edited by comp; 02-26-2014 at 04:06 PM.

  14. #14
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    hi comp, option, press "Run" button
    Attached Files Attached Files
    Last edited by watersev; 02-26-2014 at 05:14 PM.

  15. #15
    Registered User
    Join Date
    02-06-2013
    Location
    U.S.
    MS-Off Ver
    2013
    Posts
    66

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    ok so you are very close. The only problem is that there are 8 lists of numbers that need to be combined, then 8 more, and 8 more, and on and on.


    this is the code used in the G column, starting at G2


    =IF(F3=1,IF(MOD(COUNTIF(F$1:F2,1),8),LOOKUP(2,1/(F$1:F2=1),G$1:G2),MAX(G$1:G2)+1),G2+1)



    the H column needs this:


    =SUMIF(G:G,ROWS(H$1:H1),B:B)
    Last edited by comp; 03-01-2014 at 01:27 AM.

  16. #16
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    Hi Comp,

    Will the results ever be more than 9 numbers??

  17. #17
    Registered User
    Join Date
    02-06-2013
    Location
    U.S.
    MS-Off Ver
    2013
    Posts
    66

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    yes sometimes it will be longer.

  18. #18
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    On that example that you deleted, my routine makes 329 lines and Watersev's makes 247 - do you know how many lines should be made on that example?

  19. #19
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    to my understanding, if data goes in blocks of 8 groups it should be sort of that
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    02-06-2013
    Location
    U.S.
    MS-Off Ver
    2013
    Posts
    66

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    Ok, you are very very very close watersev. The first set of numbers matched exactly to what they are supposed to be. the second block of numbers does not match. This might be because sometimes the strings in the blocks can be different lengths (one or more may be shorter than the others.) I dont think this is why though.

    I have attached the worksheet that has the values configured correctly. It lists the combined values in Column H, but these blocks are listed in one long line, instead of further down the page like you did. Your way of listing the data is fine, we just need the numbers to match.

    If you look in Column K i have listed the results when i load your macro. At K300 is where the second set of results from your macro starts. You can see they do not match the H300 numbers, that is where the second block starts with my current method.

    Here is how the current my method works:

    In G1 I put a 1 and then in G2 copied down I used this formula

    =IF(F2=1,IF(MOD(COUNTIF(F$1:F1,1),8),LOOKUP(2,1/(F$1:F1=1),G$1:G1),MAX(G$1:G1)+1),G1+1)

    that will count sequentially until it reaches another 1 in column F, then reset to 1 and then count and reset....doing that 8 times until it encounters another 1, then it resets to the next number (in this case 298) and then counts through again until the next 1 in F when it resets again to 298.....repeating 8 times again, then resetting to the next integer. That can go on indefinitely.

    In Column H there is:

    =SUMIF(G:G,ROWS(H$1:H1),B:B)

    The ROWS function just returns 1 in row 1, 2 in row 2 etc. indefinitely so that allows the SUMIF to sum 8 B column values each time based on the formula in column G
    Remember, these work with the F row, which is a quotient of clomun A and E. This allows column F to have a 1 where each new string starts.


    If we can get the numbers to match for the second string it is AWESOME!!!! it runs SO FAST! My version is so slow it is unbearable.


    Very Close.xlsx
    Last edited by comp; 03-01-2014 at 12:52 AM.

  21. #21
    Registered User
    Join Date
    02-06-2013
    Location
    U.S.
    MS-Off Ver
    2013
    Posts
    66

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    Watersev, i just noticed you are in Ukraine, i hope all is well.

  22. #22
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    @Watersev - I also hope all is well with you and yours.

  23. #23
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    @comp & xladept

    Thanks for your concern. For now I'm fine. We deal with military aggression of Russia into Ukraine for now.

    @comp
    please check attachment, press "Start" button or run code "test"
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    02-06-2013
    Location
    U.S.
    MS-Off Ver
    2013
    Posts
    66

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    watersev- You are a genius! It works wonderfully The same sheet that took 7 minutes of time (when i dropped in my CSV numbers) with the CPU at 100%, now takes less than one second. I cannot tell you how much time this is going to save, it is impossible to add it all up

    The situation in Ukraine is unsettling...it is very big news here. All of our news is covering it very close. I don't pretend to know everything that is going on, the only people who really understand it are like yourself, the citizens there. I hope that no matter what happens that the Ukrainian people are allowed to live as they wish, and to do the things that make them happy.

    Now...I have one more small puzzle, but this should be easy, it is just a small change to what you are doing right now.

    This new data has two blocks that are together in each of the 8 blocks. each value is listed twice, sequentially, and needs combined to form one number, thus creating one number in the string. Then we can then add the 8 strings together.

    I have attached an example and here is how it works in the example i posted:

    this time create a "helper" column in column G that is very similar with a few changes, which i outlined in red. - put 1 in both P1 and P2 and then use this formula in P3 copied down

    =IF(F3=1,IF(MOD(COUNTIF(F$1:F3,1)-1,16),LOOKUP(2,1/(F$1:F2=1),G$1:G2),MAX(G$2:G2)+1),G1+1)

    then in Q1 use this same formula copied down for the totals

    =SUMIF(G:G,ROWS(H$1:H1),B:B)


    As you can see, the formula is very similar. I highlighted the difference between the formula listed in post#20! The second formula is actually the same.

    For easy reference, here is the original formula for the first that you made into the macro...

    =IF(F2=1,IF(MOD(COUNTIF(F$1:F1,1),8),LOOKUP(2,1/(F$1:F1=1),G$1:G1),MAX(G$1:G1)+1),G1+1)


    ...this is teh oinly other one i will need, and if you can help i would be greatly appreciative!!

    Last one2.xlsx
    Last edited by comp; 03-02-2014 at 02:34 PM.

  25. #25
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    Hi Comp,

    I think I may have solved it too (with a special handling for the last record)

    Please Login or Register  to view this content.

  26. #26
    Registered User
    Join Date
    02-06-2013
    Location
    U.S.
    MS-Off Ver
    2013
    Posts
    66

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    That is excellent xladept! That also works! it works and gives the same results

    Do you think that you could adapt it to the formula presented in post #24 as well?

  27. #27
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    Hi Comp,

    Actually, I have a correction to the routine(Last records were going to wrong accumulator):

    Please Login or Register  to view this content.
    I'll look at the #24 next And, thanks for the rep!
    Last edited by xladept; 03-02-2014 at 06:43 PM.

  28. #28
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    Hi Comp,

    Try this:

    Please Login or Register  to view this content.

  29. #29
    Registered User
    Join Date
    02-06-2013
    Location
    U.S.
    MS-Off Ver
    2013
    Posts
    66

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    You have done it again! Your dedication is commendable, you really didn't give up and came through. WOW! This will save me so much time i will have to do something to repay you Maybe i will come over and mow your yard or something


    Seriously, that was awesome. Your tenacity is amazing, thanks so much! It wont let me give you another rep on that one, but i would give you ten more if i could thanks!

  30. #30
    Registered User
    Join Date
    01-15-2014
    Location
    NYC
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    This was very helpful. Thank you everyone for the ideas.

  31. #31
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    Hi Comp,

    You're welcome! And, thanks for trying to rep me

    @ excelchautari - You're welcome and welcome to the forum

  32. #32
    Registered User
    Join Date
    02-06-2013
    Location
    U.S.
    MS-Off Ver
    2013
    Posts
    66

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    I was wondering if there were a way to convert these over to only adding together four blocks of numbers instead of 8 blocks of numbers?

  33. #33
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: =IF and =SUMIF formulas creating long long long data processing times.

    Maybe:

    Please Login or Register  to view this content.

+ 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. Excel VBA Errors For Long Processing
    By markinpt in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-30-2013, 03:55 PM
  2. Replies: 4
    Last Post: 09-22-2012, 04:21 PM
  3. processing a long list and moving content to seperate sheets
    By tsrwebman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-30-2011, 02:58 AM
  4. Replies: 4
    Last Post: 01-02-2010, 09:18 PM
  5. Long long formula not calc'ing all steps
    By jvautour in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-01-2009, 10:26 AM

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