Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 29

Thread: SUMIF on Badly Laid out Data

  1. #1
    Join Date
    16th November 2005
    Location
    NSW Australia
    Posts
    49

    SUMIF on Badly Laid out Data

    I have a spreadsheet where my source data is a Profit & Loss exported to Excel from an accounting package. Unfortunately the layout of the exported Profit & Loss report is very poor to facilitate data manipulation. I'm unable to change the layout of this report (as the accounting program doesn't give the option and the users of this spreadsheet do not have the time nor skill to reformat the layout of the Excel export into something more useable).

    An inconvenient but manageable problem is the account number is merged into the same cell as the account description (which makes account number lookups awkward). The main problem, however, is the monthly amount columns for the year are not adjacent to each other - instead they alternate with the comparative monthly column from the prior year (ie July This Year; July Last Year; August This Year; August Last Year, September This Year etc).

    I need to be able to do the equivalent of a SUMIF across multiple columns so that I can add the balances for a specified number of months for those account numbers that fall within a certain range (eg sum all accounts between codes 200 and 212 inclusive for months October to December inclusive for the current year).

    If the monthly columns for the year were adjacent to each other I could use an array formula, however, I can't see how to do this with any combination of Excel's native formulas. My only solution has been to design a custom function (UDF) but it has slowed the spreadsheet down a fair bit.

    I've attached a cut-down sample version for anybody who might have some alternatives to using a UDF. Failing that, any suggestions on how to improve my UDF would be greatly appreciated.

    My UDF code is below:

    VB:
    Function SumRangeLookup(FromCode, ToCode, Database, FromColumn, ToColumn) As Double 
         
        Dim Code As Range 
        Dim MonthColumns As Integer 
        Dim RowCount As Integer 
        Dim CalcResult As Double 
         
        CalcResult = 0 
        RowCount = 0 
         
         'Lookup code and sum the corresponding month(s) amount if the code is between the code range
        For Each Code In Range(Database) 
            RowCount = RowCount + 1 
            On Error Goto SkipCode 
            If Code >= FromCode And Code <= ToCode Then 
                For MonthColumns = FromColumn To ToColumn Step 2 
                    CalcResult = CalcResult + Worksheets("Source Data").Range("A1").Offset(RowCount - 1, MonthColumns).Value 
                Next MonthColumns 
            End If 
        Next Code 
         
        SumRangeLookup = CalcResult 
         
    SkipCode: 
         
    End Function 
    
    
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by VBA New Kid; September 11th, 2006 at 23:31.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    29th July 2006
    Posts
    224

    Re: Alternative Solutions to SUMIF Custom Function/UDF Required

    Quote Originally Posted by VBA New Kid
    I have a spreadsheet where my source data is a Profit & Loss exported to Excel from an accounting package. Unfortunately the layout of the exported Profit & Loss report is very poor to facilitate data manipulation. I'm unable to change the layout of this report (as the accounting program doesn't give the option and the users of this spreadsheet do not have the time nor skill to reformat the layout of the Excel export into something more useable).
    If this is so why don't you write a macro to open these files and re arange them into a usable format for all staff and to make it easier to use.

    you would only need a folder to dump all exports into and a simple macro to open them one by one and reformat and adding any further calcs needed before distribution.

    just an idea.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,566

    Re: Alternative Solutions to SUMIF Custom Function/UDF Required

    Would it not be possible to IMPORT the data to Excel ? It is with many packages, using Jet, ODBC etc

    Can you tell us what you're using? There are ALOT of accountants that hang out here so you may find someone's done this already

    Most Accounting packages have a "friendly" method for getting data to Excel nowadays.
    Last edited by Will Riley; September 12th, 2006 at 00:09.
    Kind Regards, Will Riley

    LinkedIn: Will Riley

  4. #4
    Join Date
    16th November 2005
    Location
    NSW Australia
    Posts
    49

    Re: Alternative Solutions to SUMIF Custom Function/UDF Required

    The accounting package is an Australian and New Zealand based program called BankLink that is typically only used by public accountants as a bookkeeping tool for their business clients. The reports that it generates are very, very basic and not at all presentable for clients.

    What I am trying to do is develop some Excel-based management reports with some graphs so that various accounting firms can provide some better reporting to their clients.

    I have spoken with the software developers at BankLink and have been told that they use a "closed database" which prevents users from gaining access to the data from external programs such as Excel - therefore there's no ODBC etc links sorry Will. The only way to do anything is to generate the report within BankLink then export it to Excel in the format in the file that I attached in my original post.

    My concern in writing macros to re-configure the data into a more useable format is the impact on the file size and also the time that it would take to execute. These concerns are from the end-user's perspective as the accounting firms have neither the time nor patience to wait for lengthy macros to process and the staff using the spreadsheet will typically be inexperienced staff so I need to make everything as simple as possible. Its also highly likely that they will replicate this file hundreds of times for their many clients so a large file size would be magnified many times and use of a lot of file server space. If a solution can be found to keep the file size down and to make the macro execution speedy, maybe this would result in a better layout and therefore avoid the need for UDF's???

    Hopefully this gives you a better background on the problem and some of the stumbling blocks.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,566

    Re: Alternative Solutions to SUMIF Custom Function/UDF Required

    Shame about the "bag of [email protected]" software ...

    Well, is there any way that you could illustrate what gets exported (i.e. the crappily formatted report) and how you would ideally like it to look in order to do your calcs.

    Then we could come up with some sort of solution
    Kind Regards, Will Riley

    LinkedIn: Will Riley

  6. #6
    Join Date
    24th March 2005
    Posts
    507

    Re: SUMIF on Badly Laid out Data

    Hi VBA New Kid,


    I think this might work for you. Input formula in cell C3 in worksheet Calculation.


    =SUMPRODUCT((LEFT('Source Data'!A10:A19,3)>=A3)*(LEFT('Source Data'!A10:A19,3)<=B3)*ISNUMBER(MATCH('Source Data'!B6:X6,$B$6:$B$7,0))*('Source Data'!B10:X19))

    Please look at the sample workbook below.
    Let me know how it turns out.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Fin Fang Foom; September 12th, 2006 at 23:13.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    16th November 2005
    Location
    NSW Australia
    Posts
    49

    Re: SUMIF on Badly Laid out Data

    This is a pretty good job thanks Fin Fang Foom, however, it errors when refering to rows outside of the data range (note that the formula needs to refer to an expanded data range as the size of the data will vary depending on the size of the exported report). Also, your formula assumes a fixed account code length of 3, however, the account code can vary between 3 and 7 and will quite often have a character (eg "/") to delineate between the account code and the sub-code.

    Also, your formula only selects the start and end months and doesn't include any months in between.

    Thanks for your suggestion though as I've been able to build on it to cater for the first of the two abovementioned problems. My expanded formula can be seen the attached file in my next post.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    16th November 2005
    Location
    NSW Australia
    Posts
    49

    Re: Alternative Solutions to SUMIF Custom Function/UDF Required

    Quote Originally Posted by Will Riley
    Shame about the "bag of [email protected]" software ...

    Well, is there any way that you could illustrate what gets exported (i.e. the crappily formatted report) and how you would ideally like it to look in order to do your calcs.

    Then we could come up with some sort of solution
    I've attached a sample file that has both the original exported source data (with the poor layout) and my manually adjusted source data (which I think is a more user friendly layout whereby the monthly columns are grouped together into their respective years instead of the original version which had the months alternating between each year).

    In the Calculation worksheet, I've entered two different formulas - one using the UDF to extract numbers from the original exported source data; and the other using an Excel native formula (an expanded version of the one kindly suggested by Fin Fang Foom) to extract numbers from the adjusted source data.

    My thoughts are that an Excel native formula from the original exported source data would be best, however, I have no idea how to do it. Any suggestions? Alternatively, what would the VBA look like to convert the original exported source data to the adjusted source data layout and having done that, can anyone suggest any improvements to my Excel native formula that I've derived?

    Many thanks in advance for any assistance.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    29th July 2006
    Posts
    224

    Re: Alternative Solutions to SUMIF Custom Function/UDF Required

    Quote Originally Posted by VBA New Kid
    My concern in writing macros to re-configure the data into a more useable format is the impact on the file size and also the time that it would take to execute. These concerns are from the end-user's perspective as the accounting firms have neither the time nor patience to wait for lengthy macros to process and the staff using the spreadsheet will typically be inexperienced staff so I need to make everything as simple as possible. Its also highly likely that they will replicate this file hundreds of times for their many clients so a large file size would be magnified many times and use of a lot of file server space. If a solution can be found to keep the file size down and to make the macro execution speedy, maybe this would result in a better layout and therefore avoid the need for UDF's???
    Hopefully this gives you a better background on the problem and some of the stumbling blocks.
    Ok so this file is not coming into your office but instead you need something to send out all over the place to re format these files?

    Why is that a firm does not have the time to get things right this does not go with what you are saying? on one hand your data needs to be presented for many many customers but there is no time?

    as above in my first post if you built a excel file that picks up all raw data from a folder / converts it and dumps it into a converted folder ready for use, i see as one of the best ways to deal with many updates as you say and to have it so that any office worker could use as the converter file would only have 1 button that says " Please update files" thats it they cant / should not go wrong.

    you talk about making it speedy and this option would have to be one of the fastest and better than opening 100's of files and updating them 1 by one.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Alternative Solutions to SUMIF Custom Function/UDF Required

    Hi,

    Define acCode

    ='Original Exported Source Data'!$A$10:$A$18

    In C1 on Calculation sheet,

    =MATCH(B1&"*",acCode,0)

    In C2,

    =MATCH(B2&"*",acCode,0)

    Define DataBase

    ='Original Exported Source Data'!$B$10:$AA$18

    Define SumRange

    =INDEX(DataBase,Calculation!$C$1,Calculation!$B$4*2-1):INDEX(DataBase,Calculation!$C$2,Calculation!$B$5*2-1)

    In C10,

    =SUMPRODUCT(--(MOD(COLUMN(SumRange)-MIN(COLUMN(SumRange))+0,2)=0)*SumRange)

    HTH
    Last edited by Krishnakumar; September 13th, 2006 at 13:54.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Sum Data By Criteria From Badly Laid Out Data
    By marshman99ca in forum Excel General
    Replies: 5
    Last Post: May 22nd, 2008, 09:43
  2. Report Of Badly Laid Out Data
    By kizzy in forum Excel General
    Replies: 2
    Last Post: February 1st, 2008, 09:47
  3. Reformat Badly Laid Out Data
    By chuzie in forum Excel General
    Replies: 28
    Last Post: December 2nd, 2007, 11:31
  4. Summarize Data Not Laid Out In Table
    By [email protected] in forum Excel General
    Replies: 2
    Last Post: November 6th, 2006, 18:40

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
  •  
porno