Forum Discussion
Automate with excel and a database.
Hi Guys,
I am currently on a project and I am kind of stuck.
Basically I am trying to figure out how I can filter an excel table using a database in the background.
Let's say I have a bank statement and I am trying to figure out what I have spend in food,
fuel and savings. i am trying to conceive something that can identify all the fuel stations by their name and automatically save them as fuel. Same with food and savings. the system will identify the name of the supermarkets and the name of my savings accounts.
See below a simplify version of what I have done manually.
You can see payments to my friends Elaine ad Mark, Payment of my taxes and payments for a car.
Currently I use the formula below to make the system understand that payments to Elaine and mark should be classified as friend.
I am trying to link this to a database and to the cells so that i can just copy the payments from the bank statement and automatically classify the expenses to their different post . All my friends will be there, my taxes, my food , and so on...
Is it possible to do this on excel?
Can anyone assist or advise??
Thanks for taking the time to read to the end.
Dimi
- Yes,I have received the rar file you uploaded.
Date Transaction details
11 5月 2022 365 Online Elaine M
relative to
Friends
Elaine
so you are going to match the category for part of the Transaction details rath and
database than full equal。
https://answers.microsoft.com/en-us/msoffice/forum/all/excel-formula-to-compare-text-in-a-cell-to/d94aca5a-e987-4786-851f-4faaf3785f44
If you have got the list of database with the structure I mentioned in previous thread,you can also apply formula easily.
I would like try sql in this situation.
12 Replies
=IF(AND(D$1="Friend",OR(ISNUMBER(SEARCH({"Elaine","Marc","Joe"},$B2)))),$C2,IF(AND(D$1="Taxes",ISNUMBER(SEARCH("Revenue",$B2))),$C2,IF(AND(D$1="Car",ISNUMBER(SEARCH("East Coas",$B2))),$C2,IF(AND(D$1="Food",OR(ISNUMBER(SEARCH({"Milk","Bread","Coffee"},$B2)))),$C2,""))))
An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell D2 and filled across range D2:G9.
- Thanks for your swift response.
I tried it and it definitely help.
However it's might be a bit much if i have 100 different variables. That's why i was thinking that having a database on the side could make things more manageable.
Any idea how i can do that too?
Regards,- much if i have 100 different variables?
What did you mean 100 different variables.
Can you share your workbook so that we are able to understand your question intuitatively?
