Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

SUMIF on Badly Laid out Data

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    Code:
    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
    Last edited by VBA New Kid; September 11th, 2006, 23:31.

  • #2
    Re: Alternative Solutions to SUMIF Custom Function/UDF Required

    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.

    Comment


    • #3
      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, 00:09.
      Kind Regards, Will Riley

      LinkedIn: Will Riley

      Comment


      • #4
        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.

        Comment


        • #5
          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

          Comment


          • #6
            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
            Last edited by Fin Fang Foom; September 12th, 2006, 23:13.

            Comment


            • #7
              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.

              Comment


              • #8
                Re: Alternative Solutions to SUMIF Custom Function/UDF Required

                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

                Comment


                • #9
                  Re: Alternative Solutions to SUMIF Custom Function/UDF Required

                  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.

                  Comment


                  • #10
                    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, 13:54.
                    Kris

                    ExcelFox

                    Comment


                    • #11
                      Re: Alternative Solutions to SUMIF Custom Function/UDF Required

                      Please see attached zip, sve to your desktop ( or anywhere on the pc )

                      open the folder and you will see a master excel file and 2 empty folders
                      1. raw data ( to put raw files into )
                      2. and a completed folder

                      Note : you do not need to send these folders as they are auto created on open file

                      i have completed most of the coding for you and left a gap for the file format part ( you just need to record how you the the file changed with a macro to form a mud macro then refine it ) then paste into this gap, if you get stck yell out but looks easy to do.

                      Then all that happens is people dump the raw files into the correct file as they recieve them, then when wanted open the file converter and press run and its all done.

                      EDIT: Changed file as there were those usual gremlins sorry about that ( should be good now )
                      Attached Files
                      Last edited by DOC; September 13th, 2006, 16:26.

                      Comment


                      • #12
                        Re: Alternative Solutions to SUMIF Custom Function/UDF Required

                        Originally posted by DOC
                        Please see attached zip, sve to your desktop ( or anywhere on the pc )

                        open the folder and you will see a master excel file and 2 empty folders
                        1. raw data ( to put raw files into )
                        2. and a completed folder

                        Note : you do not need to send these folders as they are auto created on open file

                        i have completed most of the coding for you and left a gap for the file format part ( you just need to record how you the the file changed with a macro to form a mud macro then refine it ) then paste into this gap, if you get stck yell out but looks easy to do.

                        Then all that happens is people dump the raw files into the correct file as they recieve them, then when wanted open the file converter and press run and its all done.
                        What's up Doc.

                        No attachment
                        Hope that Helps

                        Roy

                        New users should read the Forum Rules before posting

                        For free Excel tools & articles visit my web site

                        If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                        RoyUK's Web Site

                        royUK's Database Form

                        Where to paste code from the Forum

                        About me.

                        Comment


                        • #13
                          Re: Alternative Solutions to SUMIF Custom Function/UDF Required

                          Originally posted by royUK
                          What's up Doc.
                          No attachment
                          Roy it had one of those gremlins, my fault when tidying up i deleted a line to many and had to fix, sorry

                          Comment


                          • #14
                            Re: SUMIF on Badly Laid out Data

                            No problem, I just couldn't resist the opportunity to use "What's up Doc"
                            Hope that Helps

                            Roy

                            New users should read the Forum Rules before posting

                            For free Excel tools & articles visit my web site

                            If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                            RoyUK's Web Site

                            royUK's Database Form

                            Where to paste code from the Forum

                            About me.

                            Comment


                            • #15
                              Re: Alternative Solutions to SUMIF Custom Function/UDF Required

                              Originally posted by DOC
                              Please see attached zip, sve to your desktop ( or anywhere on the pc )

                              open the folder and you will see a master excel file and 2 empty folders
                              1. raw data ( to put raw files into )
                              2. and a completed folder

                              Note : you do not need to send these folders as they are auto created on open file

                              i have completed most of the coding for you and left a gap for the file format part ( you just need to record how you the the file changed with a macro to form a mud macro then refine it ) then paste into this gap, if you get stck yell out but looks easy to do.

                              Then all that happens is people dump the raw files into the correct file as they recieve them, then when wanted open the file converter and press run and its all done.
                              That's very neat Doc, thanks a lot. I agree that this will be the best way to go. I'll play around with the code to re-manipulate the raw spreadsheet then stick it into your converter file and I should be right.

                              Many thanks.

                              Comment

                              Trending

                              Collapse

                              There are no results that meet this criteria.

                              Working...
                              X