# Filtering based on multiple properties/criteria

#### therockerz

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

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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:
Thank you so much! I will have a look through and report back if I am stuck or successful.

Thank you so much Rory

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:
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?

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.

 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.

Last edited:
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.

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.

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.

Replies
1
Views
474
Replies
5
Views
511
Replies
1
Views
63
Replies
5
Views
597
Replies
3
Views
588

1,219,104
Messages
6,146,292
Members
450,685
Latest member
frederik00

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back