Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling all Excel Experts (Excel 2002)

    Calling all Excel Experts!!!!!!!

    Any help or assistance with the following would be massively appreciated!!!!

    Matching up Debits and Credits.

    1. There are several worksheets starting with ‘Debit Input’ and ‘Credit Input’ – This is where we input all of the data.

    2. There is a macro (Worksheet ‘The Bomb’) that copies the data from the ‘Debit Input’ and ‘Credit Input’ sheets and pastes the data into the ‘Debit Workings’ and ‘Credit Workings’ sheets.

    *** The reason for this is that the ‘Workings’ sheets contain the formula’s for the matching criteria. If we were to paste directly into the ‘workings’ sheets’, the spreadsheet is prone to ‘jam up’ as the formula’s on each sheet are looking to the other.

    We can be dealing with thousands of rows of data here, and consequently lots of formula’s. Sometimes many more debits than credits or vice versa.

    There are different concatenates for: criteria match 1, criteria match 2, and criteria match 3, where no match is available, this is thrown out as a non match.

    The ‘debit formula’s’ look to the credits and vice versa. There are 12 columns of formula’s (G to R) on each ‘workings’ sheet.

    EG:

    A non match on criteria 1 would activate criteria 2. A non match on criteria 2 would activate criteria 3. A non match on criteria 3 would result in an overall non match. However, where a match is generated at criteria 1, no further action would be required.

    Also, there can be duplicates. There may be 3 debits the same and 4 credits the same. As there is not an identical amount of matching debits and credits, no matchings would be required hereand all 7 items would be left unmatched.

    3. Once the macro is complete (the matchings have all been identified based upon the different criteria’s), the ‘Analysis’ sheet is activated. These are basic formula sums etc to check that there are equal amounts of matched debits and credits, and that the matched and unmatched items are equal to the input items. In other words, checking that no errors have occurred.

    4. Providing that the analysis shows no errors, there is a further macro that uses the filters on the ‘Debit Workings’ and ‘Credit Workings’ sheets, copies the data for each individual criteria, and pastes the data as special values into the output sheets, (4 sheets,1 for each criteria). The macro also removes all of the formula’s in the workbook, reducing the file size for storage.

    Please understand: This works! My point is that I built this (You should see what they used to do!!!!!! :-(), but I am a pure novice and I try to learn and pick things up as I go along. However, with thousands of rows of data and the sheer volume of formula’s, this can be very slow and time consuming. You are really unable to use your pc when this is running! My question is: Could this be more efficient, Is there an easier way, etc etc, etc?????????? Could all of this be achieved quicker???? Using VBA????? Can the 12/24 columns of formula’s be simplified / reduced????

    Any help / advice would be fantastic, and massively appreciated!!!!!!

    Ideally, I would like to attach a sample file, but even stripping the formula’s back to the bare bones, the smallest file I can get is 3.6mb. Please let me know if there would be a way of attaching this? or whether I can send a sample by email???

    Many Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling all Excel Experts (Excel 2002)

    Without seeing the workbook, I can only give some general tips:

    1) At the beginning of the macro, turn off screen updating:

    Application.ScreenUpdating = False

    At the end, turn it back on:

    Application.ScreenUpdating = True

    2) At the beginning of the macro, turn off automatic recalculation of formulas:

    Application.Calculation = xlCalculationManual

    At the end, turn it on again:

    Application.Calculation = xlCalculationAutomatic

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calling all Excel Experts (Excel 2002)

    I find it odd that you can't get a zipped file less than 3.6MB if you only have say fifty lines of debits and credits. I suspect we would only need to see one of each type of sheet and the bulk of the code.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling all Excel Experts (Excel 2002)

    Thankyou Hans, and Thanks also to Rory.

    Hans: I will give that a try, which hopefully overcomes one part of the obstacle, reducing 2 sheets and a macro!

    Rory: I'm afraid Zip files are outside of my knowledge base. Any help here also appreciated as attaching the file would be easier!

    Thanks again.....

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calling all Excel Experts (Excel 2002)

    If you are running Windows XP you can just right-click the file in Explorer and choose Send To-Compressed folder. Otherwise you need a program like WinZip - you can get trial/shareware versions at places like Tucows.com and CNet.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling all Excel Experts (Excel 2002)

    If you have Windows XP, zipping a file is built in: right-click the file, then select Send To > Compressed Folder from the popup menu.

    We wouldn't need to see the full workbook, only representative worksheets and a few rows with dummy data (don't post sensitive or proprietary data!)

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling all Excel Experts (Excel 2002)

    Again, Thankyou both!!!!

    Please find attached sample file. I guess my main query is 'Can the formula's in G-R be simplified?

    Cheers
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling all Excel Experts (Excel 2002)

    You could set calculation to manual in the Calculate tab of Tools | Options... and only recalculate (press F9) when you need all values to be up-to-date.

Posting Permissions

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