Hi All
I have this great line of code in excel that helps with my banking
=IFNA(INDEX(codes!$B$3:$B$1901,MATCH(1,INDEX(ISNUM BER(SEARCH(codes!$A$3:$A$1901,E2))*1,0,0),0)),"")
Initially I downloaded my bank statement and looked at a common string in the item bought
IE CARD PAYMENT TO TESCO STORE 2860,20.00 GBP, RATE 1.00/GBP
the common string would be CARD PAYMENT TO TESCO STORE
I store this in my codes worksheet and add a code number to it ( so if the CARD PAYMENT TO TESCO STORE comes up again it is given the same code number
I also add a category Number so I know that this item is for the food category
I have to then transfer this to access to have a meaningful conversation about my wife's spending habits
Is there a way of
Having a table with Codes IE
Description Code Category
CARD PAYMENT TO SHELL 98 43
CARD PAYMENT TO TESCO STORE 111 23
and having a table with accounts IE
01/01/2018 CARD PAYMENT TO TESCO STORE 2860,20.00 GBP, RATE 1.00/GBP ON 29-12-2017 £20.00
02/01/2018 CARD PAYMENT TO TESCO STORES 3078,6.55 GBP, RATE 1.00/GBP ON 30-12-2017 £6.55
02/01/2018 CARD PAYMENT TO WILKO RETAIL LIMIT,5.00 GBP, RATE 1.00/GBP ON 30-12-2017 £5.00
And then doing something like
lookup up CARD PAYMENT TO TESCO STORE in tblcodes and compare it to every record in Tblaccounts
If CARD PAYMENT TO TESCO STORE in tblcodes matches CARD PAYMENT TO TESCO STORE 2860,20.00 GBP, RATE 1.00/GBP ON 29-12-2017 then add the code number and category no
If CARD PAYMENT TO TESCO STORE cant be found in the tblaccounts then add a zero for further investigation
I attach a spreadsheet to give an indication of how excel does it
|
|
|