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.
 

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:
Upvote 0
Thank you so much! I will have a look through and report back if I am stuck or successful.

Thank you so much Rory :)
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top