+ Reply to Thread
Results 1 to 5 of 5

Sumif macro for raw data dump

  1. #1
    Registered User
    Join Date
    05-15-2008
    Posts
    9

    Sumif macro for raw data dump

    I was hoping I might draw on the expertise of the forum's users for assistance on a macro. I receive raw trial balance data in an excel file (see raw data tab in attached workbook). I want to sum the amounts in column D, by the financial statement line mapping in column E onto the Cons TB tab (column B) for only comp code 926 (listed in column A).

    If someone can provide this macro then I could duplicate it for the other company codes which will be listed on separate worksheets identical to the Cons TB tab.

    I only included several lines of the raw data dump, however, my file is typically 6,000 lines and the conditional sum arrays I am using really bog the file down when it recalculates.

    I'll appreciate any assistance provided.

    Thanks,
    Attached Files Attached Files
    Last edited by VBA Noob; 05-23-2008 at 02:59 PM.

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    You don't need a macro for this, you can use the SUMPRODUCT function.

    1) change the entry 'Cons TB'!B3 to be 926 (not '926)
    2) 'Cons TB'!B7: =SUMPRODUCT(--('Raw Data'!$A$6:$A$45='Cons TB'!B$3),--('Raw Data'!$E$6:$E$45='Cons TB'!A7),('Raw Data'!$D$6:$D$45))
    Copy down as required.

    HTH

    rylo

  3. #3
    Registered User
    Join Date
    05-15-2008
    Posts
    9

    Smile Sum product works, but did not fix my issue

    Thank you Rylo

    The sumproduct function works very similar to the conditional sum array I was using but I still have the same problem. The problem is that the workbook locks up while the sumproduct function or conditional sum array recalculate after the raw data tab is filtered for a particular item.

    My theory is that by using a macro, the data on the Cons TB tab would only recalculate when the macro is ran (which would only be when the raw data is updated), in contrast to the sum product functions and conditional arrays which recalculating every time the raw data tab is filtered. This would help in speeding up researching the detail for particular balances.

    The sample workbook I attached only had a few 100 rows (so the sum product recalculates rather quickly), however, the file I work with averages about 5,000.

    Thanks again, I appreciate your willingness to help!
    Last edited by Popa; 05-27-2008 at 06:08 PM. Reason: Mistyped title

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    OK, try this. It will insert the sumproduct formula and convert the results to values.

    Remember you will have to change the item in row 3 to be a number not a value.

    Please Login or Register  to view this content.
    rylo

  5. #5
    Registered User
    Join Date
    05-15-2008
    Posts
    9

    Works great!! Thanks!

    Thank you Rylo!

    That is what I was looking for. I was able to expand it update trial balances for several different markets. The macro works much quicker then the formulas and the workbook does not freeze up each time a change is made.

    Thanks again,
    Popa

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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