• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter/search multiple criteria within columns and across columns

BeachRat

New Member
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
 

Attachments

saurabhjaina211

New Member
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
 

Attachments

bosco_yip

Excel Ninja
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))),"")

72758
 

Attachments

Top