Hi there.
Here's my problem:
I have one large excel file containing one table with all billing information (Name, First Name, ... Bill Number, Amount)
I can import the bank statements as csv but the bill numbers aren't usually the only thing listed in the "payment information" column.
Is there an elegant way to get the payments from the bank statement into my billing information sheet with minimal fuss?
I wish I could upload relevant documents but can't for privacy reasons. But I hope someone has had that problem before and has come up with a solution that doesn't involve me going through every row of the statement by hand, fishing out the relevant info.
Currently I go through the printed bank statement by hand, entering the payment info (also by hand) into my excel list. This takes up a lot of my time and I can't help but wonder how many mistakes/typos I've made and not caught...
I wish I had access to accounting software, as that would eliminate all this work and also make writing reminders a lot quicker.
thanks for any and all help!
desi
I'ts going to be difficult to help without a file. Can't you upload one after eliminating the confidential pieces? Leave the structure, column names and format the same. Change names of real persons, companies etc. with fictional ones.
And with regard to the "payment information" column, please explain how you would extract the relevant payment info by hand. What logic do you apply? Etc.
Hi Desi, I do not know what your skill-level is with VBA but you could build user defined functions that read the csv file and fill the table.
You will have to account for column names and content etc and could make it as dynamic as you want.
A dummy file like Riny van Eekelen mentions would help to get the idea, doesn;t need to be large 5-10 rows would be sufficient
Add the table you would want to see filled too.
Thank you both for your help.
My week turned a little crazy and I wasn't able to check for answers until now. I'll try to get dummy files ready next week.
"please explain how you would extract the relevant payment info by hand":
I first check who's paying (Marta Mustermann) then check for the 4 digit number, if there isn't one and the payment information is something like "December 2022" or "Heimkosten Marta Mustermann" I check my billing information sheet for open bills (by filtering the name column) for that person, and usually the payment matches up (sometimes more or less) with a bill, so I enter the payment for that bill.
The complicated part is when there is no proper information, but those will just have to be done by hand anyway.
