Filtering based on multiple properties/criteria

[therockerz]

Hi all,

An urgent query and any help will be greatly appreciated.

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

Name	Is it white?	Is it edible?	Is it cheap?	Is it solid?
Apple	0	1	1	1
Milk	1	1	1	0
Car	0	0	0	1
Moon	1	0	0	1

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

 

[RoryA]

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.

 

[therockerz]

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]

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?

 

[RoryA]

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]

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]

Any help please?

	User choices--->	1	0	0	1
Criteria	Name	X	Y	Z	A
X, A	Apple	1	0	0	1
X	Orange	1	1	1	0

<tbody>
</tbody>

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

 

[RoryA]

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]

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]

OK here's a basic UDF to use in the additional column:

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.