Keeping track of donations and member payments

[Silencer]

Hello,

I'm the accountant of a non-profit - We have around 140 members who each month transfer us a membership fee, and some well wishers, whom are not members but still transfer us some money each month.

I'm trying to keep track of people paying fees and donating to us, and it's a bit of a manual thing, that I'm thinking can be done smarter?

The challenges:

1. Every month I need to look at all the payments made to us via bank transfer, which I get in a CSV file by exporting in the bank. There are 3 types of payments
   1. Member fee's & donations
   2. Non-member Donations
   3. Rent - As we're renting out a small room
2. Members and donators will sometimes write their name one month, social security the second month and a random text the third month. It's rare to have more than 3 different text from them per year.

My question:

• Is there a way I can make Excel gather the bank CSV each month and match is there any ways I can take "known" persons and text and match it to a individual member or non-member?
  • Example: I have John Tractor, I know John always transfers with text "JT", "John", "Mr. Awesome" - I'd like to be able to "match" those payments to the person I know as "John Tractor".
    • My current approach has been to create 3 look-up values for each member and non-member that contains the typical text they transfer with, and that way see from the members sheet if that person has transferred that month or not.
    • The reason the above is important is that each year, I also have to report it all to the tax authorities in order for them to get tax exception, so per "known" person, I need to compile how much they've donated to us each year and how much they've paid in fees.

In the code you can see the CSV - The "person 1" is of course 1 of 3 possible values for a specific person, that might change the month after.

Bogføringsdato;Beløb;Afsender;Modtager;Navn;Beskrivelse;Saldo;Valuta;Afstemt
31-05-2022;300;;Me;;Person 1;132855,67;DKK;
31-05-2022;100;;Me;;Person 2;132555,67;DKK;
31-05-2022;300;;Me;;Person 3;132455,67;DKK;
31-05-2022;200;;Me;;Person 4;132155,67;DKK;
30-05-2022;100;;Me;;Person 5;131955,67;DKK;
25-05-2022;100;;Me;;Person 6;131855,67;DKK;
19-05-2022;2500;;Me;;Person 7;131755,67;DKK;
17-05-2022;200;;Me;;Person 8;129255,67;DKK;
12-05-2022;125;;Me;;Person 9;129055,67;DKK;
06-05-2022;200;;Me;;Person 10;128930,67;DKK;
04-05-2022;201;;Me;;Person 11;128730,67;DKK;
03-05-2022;100;;Me;;Person 12;128529,67;DKK;
03-05-2022;50;;Me;;Person 13;128429,67;DKK;
03-05-2022;50;;Me;;Person 14;128379,67;DKK;
03-05-2022;50;;Me;;Person 15;128329,67;DKK;
03-05-2022;50;;Me;;Person 16;128279,67;DKK;
03-05-2022;50;;Me;;Person 17;128229,67;DKK;
03-05-2022;50;;Me;;Person 18;128179,67;DKK;
03-05-2022;50;;Me;;Person 19;128129,67;DKK;
03-05-2022;50;;Me;;Person 20;128079,67;DKK;
03-05-2022;50;;Me;;Person 21;128029,67;DKK;
03-05-2022;150;;Me;;Person 22;127979,67;DKK;
03-05-2022;200;;Me;;Person 23;127829,67;DKK;
03-05-2022;200;;Me;;Person 24;127629,67;DKK;
03-05-2022;500;;Me;;Person 25;127429,67;DKK;
03-05-2022;100;;Me;;Person 26;126929,67;DKK;
03-05-2022;100;;Me;;Person 27;126829,67;DKK;
03-05-2022;100;;Me;;Person 28;126729,67;DKK;
03-05-2022;200;;Me;;Person 29;126629,67;DKK;
02-05-2022;200;;Me;;Person 30;126429,67;DKK;
02-05-2022;50;;Me;;Person 31;126229,67;DKK;
02-05-2022;100;;Me;;Person 32;126179,67;DKK;
02-05-2022;100;;Me;;Person 33;126079,67;DKK;
02-05-2022;50;;Me;;Person 34;125979,67;DKK;
02-05-2022;200;;Me;;Person 35;125929,67;DKK;
02-05-2022;200;;Me;;Person 36;125729,67;DKK;
02-05-2022;201;;Me;;Person 37;125529,67;DKK;
02-05-2022;100;;Me;;Person 38;125328,67;DKK;
02-05-2022;100;;Me;;Person 39;125228,67;DKK;
02-05-2022;500;;Me;;Person 40;125128,67;DKK;
02-05-2022;100;;Me;;Person 41;124628,67;DKK;
02-05-2022;100;;Me;;Person 42;124528,67;DKK;
02-05-2022;100;;Me;;Person 43;124428,67;DKK;
02-05-2022;100;;Me;;Person 44;124328,67;DKK;
02-05-2022;200;;Me;;Person 45;124228,67;DKK;
02-05-2022;100;;Me;;Person 46;124028,67;DKK;
02-05-2022;200;;Me;;Person 47;123928,67;DKK;
02-05-2022;100;;Me;;Person 48;123728,67;DKK;
02-05-2022;100;;Me;;Person 49;123628,67;DKK;
02-05-2022;100;;Me;;Person 50;123528,67;DKK;
02-05-2022;100;;Me;;Person 51;123428,67;DKK;
02-05-2022;150;;Me;;Person 52;123328,67;DKK;
02-05-2022;100;;Me;;Person 53;123178,67;DKK;
02-05-2022;300;;Me;;Person 54;123078,67;DKK;
02-05-2022;400;;Me;;Person 55;122778,67;DKK;
02-05-2022;200;;Me;;Person 56;122378,67;DKK;
02-05-2022;100;;Me;;Person 57;122178,67;DKK;
02-05-2022;100;;Me;;Person 58;122078,67;DKK;
02-05-2022;200;;Me;;Person 59;121978,67;DKK;
02-05-2022;100;;Me;;Person 60;121778,67;DKK;
02-05-2022;200;;Me;;Person 61;121678,67;DKK;
02-05-2022;200;;Me;;Person 62;121478,67;DKK;
02-05-2022;100;;Me;;Person 63;121278,67;DKK;
02-05-2022;200;;Me;;Person 64;121178,67;DKK;
02-05-2022;200;;Me;;Person 65;120978,67;DKK;
02-05-2022;100;;Me;;Person 66;120778,67;DKK;
02-05-2022;200;;Me;;Person 67;120678,67;DKK;
02-05-2022;200;;Me;;Person 68;120478,67;DKK;
02-05-2022;200;;Me;;Person 69;120278,67;DKK;
02-05-2022;200;;Me;;Person 70;120078,67;DKK;
02-05-2022;200;;Me;;Person 71;119878,67;DKK;
02-05-2022;3450;;Me;;Person 72;119678,67;DKK;
02-05-2022;100;;Me;;Person 73;116228,67;DKK;
02-05-2022;100;;Me;;Person 74;116128,67;DKK;
02-05-2022;100;;Me;;Person 75;116028,67;DKK;
02-05-2022;400;;Me;;Person 76;115928,67;DKK;
02-05-2022;50;;Me;;Person 77;115528,67;DKK;

 

[Silencer]

If it's relevant - I use another sheet to keep all the member, non-member, and renter information - Like name, member number etc.

 

[Silencer]

Poke - Anyone help out here? Kind of at my wits end on this