Jun 26 2023 12:53 PM
Jun 26 2023 12:53 PM
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.
Jun 26 2023 01:39 PM
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.
Jun 27 2023 02:06 PM
Jun 27 2023 02:20 PM
Jun 27 2023 03:32 PM
@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.
Jun 28 2023 02:41 PM - edited Jun 28 2023 02:43 PM
re: tried to upload the actual Xlsx file but the system does not allow it.
zip the excel .xlsx file then ,then Open full text editor and Browse files to attach.
or upload your zipped file to http://e.anyoupin.cn/EData/?p=tools.ceshi.index/uploadFileForm
According the screenshot,you can create a help list to tag your original cost items.
help list/database structure like below
car east cost
rather than the second picture you uploaded
Then you can reclassify the original cost list item and output pivottable or sql to two dimension table at last.
Jun 30 2023 04:42 PMSolution
Jul 03 2023 11:46 AM
Jul 04 2023 01:23 AM
Thanks for your swift response.
by "scale" i meant the amount of conditions. I added two more condition for "Bob" and "Other" and i got the msg i shared in the previous email.
Now about Cbm. Do you mind talking me trough it? I am a bit confused about what the workings below were trying to achieve.
Jul 04 2023 02:19 PM