Forum Discussion
Automate with excel and a database.
- Jun 30, 2023Yes,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.
=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.
- Dimi237Jun 27, 2023Copper ContributorThanks 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,- peiyezhuJun 27, 2023Bronze Contributormuch 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?- Dimi237Jun 27, 2023Copper Contributor
peiyezhu Thanks for this.
I tried to upload the actual Xlsx file but the system does not allow it.
So here is the screenshot for the sheet.
basically using the "If" Formula, i can put most things in... However, as you can see for "Others", there is more variables.
If I had 50 more different variables for "Others" i think it might make the formula harder to manage.
Which is why i was thinking of setting up a database like below.
So that i can just include in the formula every element of the database.
therefore, instead of listing the different variables that could qualify for "others" i can just refer to the table above and sort it out.
Not sure if that makes sense to you.
let me know if you get what i mean.
Dimi