• 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.

Filtering Large Amounts of Data

DME

Member
Excel for Mac 2011 user here.

I have a large amount of data - about 3,000 rows - in the first tab of a workbook. The data is not setup in a table and I cannot change the formatting.

I want the second tab to be able to filter the data from the first tab based on multiple criteria (up to 3) as selected by the user, as well as date range criteria.

I've attached a sample file that provides further details and explanation. I'm open to VBA, formulas, or going in a completely different direction if there are any other suggestions of how best to approach this. In Excel for Mac 2011 I cannot use ActiveX controls though. I'm still learning it myself (used to Excel for PC) so there could be other limitations too that I'm just not familiar with yet.

Thanks in advance for your thoughts!
 

Attachments

  • Sample File.xlsx
    43.7 KB · Views: 3
I'd suggest limiting your selections of Drop Downs 1-3 to 3 defined fields
Leaving them all open massively complicates the requirement

eg: If you make C5 the Project Type
on B11 you can use:
=IF(COUNTIFS(Data!$A:$A,">="&$C$2,Data!$A:$A,"<="&$C$3,Data!$I:$I,$C$5)<ROWS($B$11:$B11),"",INDEX(Data!$A:$A,SMALL(IF((Data!$A$2:$A$25>=$C$2)+(Data!$A$2:$A$25<=$C$3)+(Data!$I$2:$I$25=$C$5)=3,ROW($A$2:$A$25)),ROW($A1)))) Ctrl+Shift+Enter Copy down

But this is only using the dates and 1 field for filtering
upload_2014-11-26_9-34-8.png

see attached file


 

Attachments

  • Sample File.xlsx
    12.1 KB · Views: 4
  • Like
Reactions: DME
Back
Top