Forum Discussion

Dimi237's avatar
Dimi237
Copper Contributor
Jun 26, 2023

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 s...
developer
Formulas and Functions
Macros and VBA
OliverScheurich's avatar
OliverScheurich
Gold Contributor

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.

Dimi237's avatar
Dimi237
Copper Contributor
Jun 27, 2023
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,
  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Jun 27, 2023
    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?
    • Dimi237's avatar
      Dimi237
      Copper Contributor
      Jun 27, 2023

      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

       

      • peiyezhu's avatar
        peiyezhu
        Bronze Contributor
        Jun 28, 2023

        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.

         

Resources