Results 1 to 8 of 8

20070930, 01:02 #1
 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

20070930, 01:23 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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

20070930, 01:26 #3
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 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

20070930, 01:32 #4
 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.....

20070930, 01:34 #5
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 Posts
Re: Calling all Excel Experts (Excel 2002)
If you are running Windows XP you can just rightclick the file in Explorer and choose Send ToCompressed 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

20070930, 01:35 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Calling all Excel Experts (Excel 2002)
If you have Windows XP, zipping a file is built in: rightclick 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!)

20070930, 03:02 #7
 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 GR be simplified?
Cheers

20070930, 13:04 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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 uptodate.