Excel VBA code to filter datatable based on list of items

[xiaonajia]

I have a data table and I want excel to filter the rows based on a list of items on another sheet.

For example, in the table, there are apple, apple, orange, pear, papaya.
Then on another sheet, I will dynamically type apple, pear in a column.
Once I activate the VBA code, it should show me the filtered rows in the table based on the column entries on another sheet, i.e. apple, apple, pear rows (show for multiple entries if there's same name)

https://www.extendoffice.com/documents/excel/4113-excel-filter-based-on-list-selection.html
Something as above, but I wasn't able to use this advanced filter function despite following the instructions, so I wasn't able to record a macro.

There are many more online tutorials but they are not based on VBA code or easy to use. One method was doing a countif, but the thing is my datatable will be dynamically expanding which means I have to drag and expand the countif or edit the formula each time. (besides, i dont like extra columns in my excel for viewing purpose so prefer doing this via VBA code or recording macro)

Does anyone know how to do this easily?

 

[davidktw]

I have a data table and I want excel to filter the rows based on a list of items on another sheet.

For example, in the table, there are apple, apple, orange, pear, papaya.
Then on another sheet, I will dynamically type apple, pear in a column.
Once I activate the VBA code, it should show me the filtered rows in the table based on the column entries on another sheet, i.e. apple, apple, pear rows (show for multiple entries if there's same name)

https://www.extendoffice.com/documents/excel/4113-excel-filter-based-on-list-selection.html
Something as above, but I wasn't able to use this advanced filter function despite following the instructions, so I wasn't able to record a macro.

There are many more online tutorials but they are not based on VBA code or easy to use. One method was doing a countif, but the thing is my datatable will be dynamically expanding which means I have to drag and expand the countif or edit the formula each time. (besides, i dont like extra columns in my excel for viewing purpose so prefer doing this via VBA code or recording macro)

Does anyone know how to do this easily?

Seems like not much response with response to your VBA request.
Here is an example using FILTER and MATCH that will return Dynamic Arrays which may work for you
as there is no limit to the number of rows the formula will applies.
It works for Microsoft Office 365

https://mega.nz/file/PWRnDCSD#cDczybE_wdcFaQFECYcFCqcO83ZFxEtcyB3sRXyX3bM
Play with it and see if it suit your needs.
1) The filter column can be placed in another worksheet if you like.
2) You can create a separate worksheet for your VIEWING purpose and your data table in another worksheet.

I may be mistaken but it wouldn't be feasible to have your data table "view" modified without changing your data in your data table, unless you are using the native FILTER feature which is more like hiding rows/cells instead of changing the cells values which a formula or VBA code will.
Hence you will need to separate your data table and your view table, which you can do so by placing them in different worksheet, or if you prefer, just hide the data table columns(or rows), depending on how you are structuring your tables.

 

[xiaonajia]

Seems like not much response with response to your VBA request.
Here is an example using FILTER and MATCH that will return Dynamic Arrays which may work for you
as there is no limit to the number of rows the formula will applies.
It works for Microsoft Office 365

https://mega.nz/file/PWRnDCSD#cDczybE_wdcFaQFECYcFCqcO83ZFxEtcyB3sRXyX3bM
Play with it and see if it suit your needs.
1) The filter column can be placed in another worksheet if you like.
2) You can create a separate worksheet for your VIEWING purpose and your data table in another worksheet.

I may be mistaken but it wouldn't be feasible to have your data table "view" modified without changing your data in your data table, unless you are using the native FILTER feature which is more like hiding rows/cells instead of changing the cells values which a formula or VBA code will.
Hence you will need to separate your data table and your view table, which you can do so by placing them in different worksheet, or if you prefer, just hide the data table columns(or rows), depending on how you are structuring your tables.

if my datatable has more than 1 column, will it still work?

for example, the datatable has name, weight, price etc

PS I should have been clearer in my question and example

 

[davidktw]

if my datatable has more than 1 column, will it still work?

for example, the datatable has name, weight, price etc

PS I should have been clearer in my question and example

Added new tabs for demonstration.
There is also a performance tab for if you limit the rows limit(eg:10000) it will be faster, or else it will need to examine through 1M rows / column for each filter.

Actually you might get better performance if you are using database instead of excel. No doubt you can do all these in excel.
but a database is designed to do this really really well for millions of rows; So well that you will get your output in sub seconds.

https://mega.nz/file/HWoywCaJ#BVzJYZV-7RCC9e3JDqf8JxLDdD8wzS0P36NHIowdxnA

What you want can be easily realised with the following SQL

SELECT *
FROM DATA_TABLE
WHERE FILTER_COLUMN IN (
  SELECT * FROM FILTER_TABLE
);

This article might interest you
https://www.cdata.com/kb/tech/mysql-odbc-excel-query.rst

 

[xiaonajia]

Added new tabs for demonstration.
There is also a performance tab for if you limit the rows limit(eg:10000) it will be faster, or else it will need to examine through 1M rows / column for each filter.

Actually you might get better performance if you are using database instead of excel. No doubt you can do all these in excel.
but a database is designed to do this really really well for millions of rows; So well that you will get your output in sub seconds.

https://mega.nz/file/HWoywCaJ#BVzJYZV-7RCC9e3JDqf8JxLDdD8wzS0P36NHIowdxnA

What you want can be easily realised with the following SQL

SELECT *
FROM DATA_TABLE
WHERE FILTER_COLUMN IN (
  SELECT * FROM FILTER_TABLE
);

This article might interest you
https://www.cdata.com/kb/tech/mysql-odbc-excel-query.rst

oh my god, can't believe this worked so beautifully!

Unfortunately, I'm not that familiar with using database as SQL is not my strongest language. I like excel because it allows me to color stuff as well as having other functions that Access doesn't offer but of course, this means some drawbacks as well.

My database has less than 10k rows for now, but it should be good enough for me. I'll just archive obsolete data rows to somewhere or edit the code to increase the 10k limit to see the maximum limit for my computer before I start noticing performance issues.

Thanks David senpai!!