Filter function with if statement for multiple selection from multiple lists

Question

Hi there,

I have the following list prepared

Business Vertical
Location
Director
Manager
Group

I want to use filter formulat to display the values based on the selection from the above lists. These are 5 lists and I want these lists to display the values based on the selection. For example,

If all of these are blank then show all the data, if Business Vertical has some value then it must show based on that only with the rest of the data.

If Business Vertial is blank and Location is filled as Europe then all the data from Europe should be shown irrespective of other values.

But if the Business Vertival and Europe is filled then it must show that vertval from Europe.

Likewise for all the other fields.

Can someone help me with this please?

Answer 1

=SORT(FILTER('Data-Mar-23'!B:N,((IF(D2="",1,'Data-Mar-23'!N:N=D2)*(IF(D3="",1,'Data-Mar-23'!F:F=D3))*(IF(D4="",1,'Data-Mar-23'!M:M=D4))*(IF(D5="",1,'Data-Mar-23'!L:L=D5))*(IF(D6="",1,'Data-Mar-23'!C:C=D6))))))

Answer 2

It is far too complicated to recreate such a scenario. And if it does not match yours, then our solution will not work for you.

For this kind of requests, please create a sample file with the layout of your original file, filled with sample data and colored cells with the expected result.

At best make a copy of your original file and anonymize the necessary data. For this please download this file
https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1
Open your Excel file
Right-click on the sheet tab
Choose "View Code"
Press CTRL-M
Select the downloaded file and import
Close the VBA editor
Select the cells with the confidential data
Press Alt-F8
Choose the macro Anonymize
Click Run

Upload it on OneDrive (or an other Online File Hoster of your choice) and post the download link here.
https://support.office.com/en-us/article/Share-OneDrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

Then we can look at the file and try to find a solution. Thank you for your understanding.

Andreas.

Hi @Andreas Killer

I have uploaded the file to this link: Sample.xlsx See the sheet Sample which has all the filter formula and lists that I have tried.

This may help you help me.

Thanks much for your prompt response.

Answer 3

Use if Location = "', True, else A2:A7=G2, Same as others.

Here is an sample.
=FILTER(A2:D7,(IF(G2="",1,A2:A7=G2))*(IF(G3="",1,B2:B7=G3))*(IF(G4="",1,C2:C7=G4))*(IF(G5="",1,D2:D7=G5)))

Many thanks for you response @Snow Lu MSFT but it did not work with my data.

I have a list of items and I want them to work with all these fields. I can share a file which I am using if that can help you.

Here is my code which works with my the first condition only. Rest works with this formula if I keep all the fields filled up. But I want any or all of the feilds criteria to show me results as expected.

=SORT(UNIQUE(IF(D2="",TAKE('Data-Mar-23'!B3:N1048576,'Data-Mar-23'!BK2),FILTER('Data-Mar-23'!B:N,(('Data-Mar-23'!N:N=D2)*('Data-Mar-23'!F:F=D3)*('Data-Mar-23'!M:M=D4)*('Data-Mar-23'!L:L=D5)*('Data-Mar-23'!C:C=D6))))))

Here is the link of the file which you can access to help me: Sample.xlsx See the sheet named Sample where I have applied the formula.

Answer 4

Use if Location = "', True, else A2:A7=G2, Same as others.

Here is an sample.
=FILTER(A2:D7,(IF(G2="",1,A2:A7=G2))*(IF(G3="",1,B2:B7=G3))*(IF(G4="",1,C2:C7=G4))*(IF(G5="",1,D2:D7=G5)))

Answer 5

It is far too complicated to recreate such a scenario. And if it does not match yours, then our solution will not work for you.

For this kind of requests, please create a sample file with the layout of your original file, filled with sample data and colored cells with the expected result.

At best make a copy of your original file and anonymize the necessary data. For this please download this file
https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1
Open your Excel file
Right-click on the sheet tab
Choose "View Code"
Press CTRL-M
Select the downloaded file and import
Close the VBA editor
Select the cells with the confidential data
Press Alt-F8
Choose the macro Anonymize
Click Run

Upload it on OneDrive (or an other Online File Hoster of your choice) and post the download link here.
https://support.office.com/en-us/article/Share-OneDrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

Then we can look at the file and try to find a solution. Thank you for your understanding.

Andreas.