Automate with excel and a database.

[Dimi237]

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

[#2]

@Dimi237 

=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.

[#3]

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,

[#4]

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?

[#5]

@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

[#6]

@Dimi237 

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

type item
other health
other barna
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.

[#7]

@peiyezhu 

Just uploaded it.

Pelase let me know if you can access it..

Link here

[#8 · Accepted solution]

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/d9...

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.

[#9]

Thanks, i think this link is also very helpful. I do believe that your first response answer most of it as well so thanks a million for your time and effort.

D

[#10]

@peiyezhu Actually i am now starting to increase the scale of it  and it says that the level of nesting is more than I could 

Any idea how to tackle this?

[#11]

increase the scale of it
I can not get what you mean increase the scale.
In fact I use sql to accomplish this kind of task like below.
http://e.anyoupin.cn/EData/?s=894

[#12]

@peiyezhu 

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.

Regards, 

[#13]

@Dimi237 

https://b23.tv/0ZwG6AP

 This is a vedio about SAAS sql。

http://e.anyoupin.cn/EData/?s=Automate

This is the SAAS input site.

The attached is a demo raw data structure for upload.

If possible，submit your 2 sheets to fetch the output report.