Filtering based on multiple properties/criteria

therockerz

New Member
Joined
Mar 2, 2014
Messages
12
Hi all,

An urgent query and any help will be greatly appreciated.

I have a spreadsheet (with data) with the following structure:

NameIs it white?Is it edible?Is it cheap?Is it solid?
Apple0111
Milk1110
Car0001
Moon1001

<tbody>
</tbody>

Apologies for the poor example data.

What I need it to do is based on a checkbox input where user will select whether they want to see white/cheap/edible/solid or a combination, and this list will be filtered based on that.

For example, user selects white and/or edible - the result on a different page should be:

Name
Apple
Milk
Moon

<tbody>
</tbody>


Any thoughts on how I can achieve this? In my example, there are just three criteria, but in my work sheet I have around 200 criteria, so advanced filter with "OR" structure would take up too many rows.

Any solution whether VBA/not would be really appreciated!

Thanks.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,487
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I'd use an advanced filter but use a formula for the criteria rather than separate rows.

I've put a simple example file on OneDrive here.
 
Last edited:

therockerz

New Member
Joined
Mar 2, 2014
Messages
12
Thank you so much! I will have a look through and report back if I am stuck or successful.

Thank you so much Rory :)
 

therockerz

New Member
Joined
Mar 2, 2014
Messages
12
I am trying to make the criteria get populated based on checkbox values from another sheet - and I am now trying to parse the checkbox values to those cells on top of the columns (i.e. Criteria).

Could you please tell me how this code works so I could develop it further? I am not sure what the purpose of the SUMPRODUCT formula is.

Many thanks

PS. Reason I ask is that if the cells are "IF(B1=TRUE,1,"")" then it doesn't seem to work. Any solutions?
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,487
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

The SUMPRODUCT formula calculates whether any column in a row matches the criteria set (it has to exclude blanks or the 0s would match them).

The code just adds a 1 to any cell where the checkbox is checked and then updates the filter.

Can you post a workbook showing your actual setup somewhere if you're still having problems?
 

therockerz

New Member
Joined
Mar 2, 2014
Messages
12
Hi RoryA and all,

I am trying to build on RoryA's example file as it was very helpful and I need one small feature added in:

Considering the column headers were White, Edible, Cheap, Solid - I want another column for each Item (Apple, Milk) that shows all matching properties .

In this example, the cell next to Apple would show "Edible, Cheap" if the user had selected to see all items which were edible and cheap.

I understand the logic on how to apply this, but can't seem to get the right codes!!

Any help is greatly appreciated.

I basically want it to do:

(If (Row A = Row C), then Row B) where Row A is the criteria set, Row C is the properties for each item, and Row B is the name of the properties.
 

therockerz

New Member
Joined
Mar 2, 2014
Messages
12

ADVERTISEMENT

Any help please?


User choices--->1001
CriteriaNameXYZA
X, AApple1001
XOrange1110

<tbody>
</tbody>


I want the cells in Criteria to be populated based on matching criteria (X, Y, Z, A) with the user choice.
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,487
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It would be very clunky to do this with formulas unless you only have a couple of columns. A function written in VBA would be a better bet - if I have time later today I'll try and put one together for you.
 

therockerz

New Member
Joined
Mar 2, 2014
Messages
12
I am okay with VBA for this.

I have, for now, used for loops to run through each row and each column to see if it matches the criteria and "if" it does, then it adds the criteria (e.g. X,Y,Z,A) to the cell.

I am sure there is a more elegant way of doing it (may be with arrays) but can't get it into VBA.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,487
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
OK here's a basic UDF to use in the additional column:
Code:
Function FilterMatches(rngFilters As Range, rngData As Range, rngHeaders As Range, Optional strDelim As String = ",") As String
    Dim n As Long
    Dim strFilters As String
    For n = 1 To rngFilters.Count
        If rngFilters(n) = 1 Then
            If rngData(n) = 1 Then strFilters = strFilters & strDelim & rngHeaders.Cells(n).Value
        End If
    Next n
    If Len(strFilters) <> 0 Then FilterMatches = Mid$(strFilters, Len(strDelim) + 1)
End Function

So, if you have the filter 1s and 0s in C1:F1, the table headers in C2:F2 and the data starting in C3:F3, in A3 enter:
=filtermatches($C$1:$F$1,C3:F3,$C$2:$F$2)
and copy down.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,633
Messages
5,523,995
Members
409,555
Latest member
TIPSAREA

This Week's Hot Topics

Top