There is a club who's members pay monthly - pretty much all at the same time (which means downloading bank statement for those days is very difficult as it will only allows a certain amount of transactions.....I have to download a page at a time and knit together) and they used to update a database manually with the payments.
I came along and thought I could 'help' by building a table with all members names and details on one page and importing bank statements on the next tab...in Excel. By cleaning up the direct debit/standing order references and copying into members page, I have been able to link the two sheets and run SUMIFS in order to book transactions by individual and by month. Great....although the knitting together of the bank statements and getting rid of erroneous charcters on DD/so references is a bit time consuming.
There are close to 500 members payments however so the bank statement has been growing considerably over the past few months and Excel has been crashing continously and doing woerd things. For example, every month even when the correct reference is on the bank statement tab, it only 'matches' the name on the first sheet when I copy and paste into second sheet from the first sheet - as I said it is the same but don't know why Excel doesn't always see this??? This happens for around 10% of them. Luckily I run a duplicate conditional format so I can see which ones haven't been picked up and these appear as unique (even though they are not!) so I know they are to be addressed.
Anyway.....hoping all of this is clear??......is there a better way of doing this?? I DO want to avoid having someone manually inputting receipts into whatever the software is, but also realise that Excel is not up to the job. I could 'kill' a lot of the formulae when I complete past months (by copying and pasting values only) but not sure I want to and not sure this would make life much easier for Excel???
In order to show all members and all payments in a clear table is the goal so what approach would you take for this and what software would you employ??? Is there better software to specifically handle this? can I use the likes of Xero or other accounts software to handle the bank statement import better? Am I using Excel inefficiently???
Not an answer to the excel part just now, but from the Bank perspective - which Bank? Any decent Bank will be able to provide you with a fully downloadable Bank statement in excel format which runs to one page from the date of the last Bank statement to the period you are looking at. I would suggest you dont rely on the Bank downloads on the actual day of the entries as the payments can easily be recalled the same day and therefore not actually paid. Might also be worth actually getting an upgrade from the standard online option to something that can produce the 'goods' for you - some of the Banks do enhanced online banking software that can be fully integrated with for example sage, but its done via better csv/excel reporting that than the standard so would probably integrate better with your current excel or even better with an accounting software package of some description. You would need to speak to someone non branch based though as frankly they have no clue! Although the club might not want to pay for such anyway.
__________________
Joanne
Winner of Bookkeeper of the Year 2015, 2016 & 2017
Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.
You should check out answers with reference to the legal position
The bank is HBOS and it is a bit annoying. I have come across banks that obnly offer three months period for example, but limiting transactions is a bit of a pain.....good idea to ask the bank for help!! I need to go to them in any case as I have to find out how and why the references include a lot of gobbledygook! Not all banks but around 40% need to be cleaned up of the extra date and reference numbers. Others just say 'Jane Smith' for example (maybe depending on which bank it came from?).
I suppose if I got the right download and help from the bank, I could upload it into some accounting software that can interpret the entries - once the rules are established.Then just a case of finding the best way of sharing info with club.
As an aside - I have updated the spreasheet on a PC and it doesn't seem to crash like it does on my laptop - although laptop better/more powerful!! It does however still have the same problem not recognising entries that are exactly as they should be!
Oh Im sorry I thought you said it was a Bank!!!!!!!! HBOS, a glorified buidling society with numpty refrences!!!!!!!!!!!!!!! Sorry, just joshin with ya, although Ive worked for a proper Bank and well........ Shaun will get me.
The references in part are down to the member in that they can ask for a proper reference to be quoted on the payment, ie a membership number, but getting them all to do that is near on impossible. I know as a Treasurer for an organisation!
Good luck with help from the Bank -but dont let a counter numpty put you off, seriously they know nothing! You might need to speak to someone at their commercial level. Bet you end up with a dent in your head!
Good luck with it!
__________________
Joanne
Winner of Bookkeeper of the Year 2015, 2016 & 2017
Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.
You should check out answers with reference to the legal position
Count1314 wrote:It does however still have the same problem not recognising entries that are exactly as they should be!
I have heard that so many times.
Something isn't how it should be.
There is a term amongst developers called "code blind" in that you can look at something for so long that you cannot see it.
Go through each step logically, check that when you copy it is not taking accross an absolute reference back to the first sheet.
It will be something obvious when you find it.
But, before spending too much time on the existing sheet consider whether you are approaching the problem in the right way.
I believe that you need to create the feed in a data table and then use a pivot table to extract the details. It will need three worksheets in the spreadsheet, one that holds the data table, one as a work area to reformat bank statements before copying them into your data table, one to hold the pivot table.
To split fields that you have extracted simply insert a unique character (/ generally works well) and use text to columns to extract the bit that you want remembering that a field with a space at the end of it is not the same as a field that doesn't have a space at the end of it so always end on the last character, not after a space.
If you are uncomfortable with Pivot tables there is an Excel chanel called ExcelisFun which is a lot more in depth than the title would suggest. It's mentioned in the free stuff thread but for ease of reference here's a link to get you started on Pivot tables : https://www.youtube.com/watch?v=i67XK3qjL_w
Mastering the use of Pivots is probably THE most essential thing that you will ever do with Excel
Have fun,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Oh Im sorry I thought you said it was a Bank!!!!!!!! HBOS, a glorified buidling society with numpty refrences!!!!!!!!!!!!!!! Sorry, just joshin with ya, although Ive worked for a proper Bank and well........ Shaun will get me.
Yep, lol.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
I know exactly what you are saying, but I don't think it is code blindness.
All the 'names' are downloaded from the same place in the same format - yet some are recognised as being the same as in my sumifs formula on the other sheet and others not recognised. There shouldn't be any formatting differences and/or absolue reference problems........in fact no problem with the 'numbers'. It is just bizarre and annoying.
I have seen some problems running sumifs before although it generally works ok. I ran one recently where I was summarising in a table (yes I know - Pivot Tables!!) underneath a short list of data. The totals of the list and the table did not agree and after re-typing the category manually in either the table or the list for just one of the categories - perhaps just one cell! - it worked!!?? It was a short list and easy to figure out but I would love to know what is happening with this - it seems to be text rather than numbers where the problem lies.
I can't see pivot tables working unless the names tie up with the member list.....will still have the same problem although I agree that they are v useful.
I'd be happy to have a look to see why something isn't matching if you could send me (contact details below) an example of two lists that exhibit the problem behaviour. I've a fair bit of experience in this area, so I'd like to think I could find the issue for you if you were able to share some of the problem data.
I assume it's not a problem with Automatic/Manual calculation? Your description above almost sounded like the change fired a calculation that, for some reason, hadn't taken place. No problems with circular references either?
The main problem you were referring to in the original post (identical items not matching) was caused by trailing spaces. Unfortunately "Ian Brown" is not identical to "Ian Brown " even though the trailing space on the second one is invisible to the human eye. Once a TRIM() function was applied to both lists, the matching worked flawlessly.
I've reconciled the file for you to prove that the matching is reliable.
Good luck with maintaining the monster spreadsheet going forward.
P.S. It might speed up calculation time if you were to change formula references in the $A:$A format to read, say, $A2:$A10000. I think I saw somewhere (Charles Williams' site?) that whilst $A:$A didn't introduce too much of an overhead in Excel 2003 (approx 64000 rows), it does in Excel 2007 and above (approx 1024000 rows).
Kind regards,
-- Edited by Onion4Sage on Thursday 21st of April 2016 04:08:18 PM
I've just replaced all the SUMIFS() in your spreadsheet with GETPIVOTDATA() functions having put your statement data into a pivot table. Your spreadsheet no longer creaks. In fact I'd say recalculation is almost instantaneous now!
I hope the new approach serves your purposes well.
Yes - Just wanted to reply publicly on the forum to Ian (we have been emailing privately).
Ian is indeeed a star and has been a massive help. Even the initial issue he found went a long way to solving my problem. I had looked this previously but unfortunately wasn't confident it was an issue and didn't apply Trim to both lists! He went well beyond what I was hoping he would do for me and it will be very useful in general going forward with Excel.
