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
