Filter/search multiple criteria within columns and across columns

[BeachRat]

Hello,
I am working on building a database, of sorts, in Excel.

As a requirement we need to be able to filter by/return a set of results from multiple criteria within individual columns and across multiple columns.

Ideally we need to be able to perform this task using excel online which does not appear to support macros or advanced filtering.

In order to save on the number of rows, criteria may be contained within a substring (keywords, in this example).

So, in the attached example table, I want to view all results containing 2010 in column C OR keyword in column D 1 OR keyword 3 in column D.

This would return rows 2, 3, 5, 6 & 7.


Is this possible in the online environment? Is it even possible in the desktop version?
I appreciate any advice or pointers you can give me as I’ve been trawling the internet and coming up with nothing fruitful.

Cheers

 

[saurabhjaina211]

Hi,

Conditional formatting can be used. VBA is the best way to do this.

Attached the example file where I used conditional formatting with single keyword option (2010 and Keyword 1).

Regards,
Saurabh

 

[bosco_yip]

Try,

In "Criteria" F1:G3, enter Year (2010) and Keywords (Keyword 1 and Keyword 3)

In " Result" F7, formula copied across right and down :

=IFERROR(INDEX(A:A,AGGREGATE(15,6, ROW($A$2:$A$7)/((($C$2:$C$7=$F$2)+ISNUMBER(SEARCH($G$2&";",$D$2:$D$7&";"))+ISNUMBER(SEARCH($G$3&";",$D$2:$D$7&";")))>0),ROW($A1))),"")