How to Advanced Filter to cells with multiple values?

[RyanF]

Morning,

I'm new to the Mr Excel forums but am in a bit of a loggerhead with a searchable/filterable database I am trying to set up. I have a rough understanding of VBA (mostly I record macros to see what the code looks like then edit it it suit my needs).

I'm trying to set up a a spreadsheet where a user can enter search terms to filter a list of data. I'm using Advanced Filter so I can use wildcards and operators (>, < etc) and copying the results to a "results" sheet.

My problem is that some of the data entries have multiple values associated with them and this makes the filters values act funny. I have come up with two ways of setting out the list so far but both have faults:

Option 1: Use Alt+Enter to separate multiple values ie:

Description	Asset #	Site	Product Type	Speed	Comments
Machine A	1234	A	375 500	1500 1000	xxxxxxxxxx
Machine B	2345	A	500 1000	1000 800	xxxxxxxxx
Machine C	3456	B	1250	500	xxxxxxxxxx
Machine D	7890	B	375	1000	xxxxxxx

<TBODY>
</TBODY>

I can filter this table for machines with a speed of 1000 using the advanced filter parameters:

Description	Asset #	Site	Product Type	Speed
				1000
				*1000
				1000*

<TBODY>
</TBODY>

The user gives an input of "1000" and the spreadsheet automatically adds the wildcard * on the two rows below so that the advanced filter looks for Speed = 1000 OR *1000 OR 1000* (this collects the speeds 15001000 and 1000800) I would get this as a result:

Description	Asset #	Site	Product Type	Speed	Comments
Machine A	1234	A	375 500	1500 1000	xxxxxxxxxx
Machine B	2345	A	500 1000	1000 800	xxxxxxxxx
Machine D	7890	B	375	1000	xxxxxxx

<TBODY>
</TBODY>

The Problem: I can't think of a way to use operators like > or <. eg if I used <1400 I would only get Machine D as the filter thinks Machines A and B have speeds of 15,001,000 and 1,000,800 respectively. Applying the advanced filter with *<1400 OR <1400* doesn't return a result

Option 2: Cells without multiple values are merged (the cells containing "Machine A" or asset "2345" would be two rows merged for eg)
This means that if I were to filter with the parameters Speed: >=850 I would get:

Description	Asset #	Site	Product Type	Speed	Comments
Machine A	1234	A	375 500	1500 1000	xxxxxxxxxx
Machine B	2345	A	500	1000	xxxxxxxxx

<TBODY>
</TBODY>

The Problem: Machine B has two speeds but only one matchs the filter criteria so only one of the merged rows is shown. Plus merged cells are going to get messy over a database with 5000+ assets...


This thread is already quite long but please let me know if I can provide more info to help

 

[RyanF]

I was taking 1500 500 as speeds >=500 AND <=1000 (in other words speeds inside the interval 1500 500), not as =500 OR =1000

Could you clarify?

M.

Unfortunately I am trying to get the spreadsheet to do both.
I want the filter to treat criteria entered as 1000 as =1000
BUT if the user wants to see machines that can do speeds greater or equal to 1000 they would enter >=1000

Background: I'm trying to make my spreadsheet as user-proof as possible. The user enters their criteria on a separate, locked sheet with very speciic input cells.

A machine can run only one speed for a given product type, (hence the =1000), but what if they want to mark all machines less than 1000 speed regardless of product type for deletion? Then they would need to filter for <=1000

It's not the end of the world if the spreadsheet lacks this functionality - it's just a nice-to-have

 

[Marcelo Branco]

hmm...so you do want, for example, either =1000 or >=1000.

have to re-think

 

[RyanF]

Correct. It's a bit tricky haha

 

[Marcelo Branco]

Maybe this

Alt+F11 to open the VBEditor
Insert > Module
Paste the code below (User Defined Function) in the right panel

Function Eval(r1 As String, r2 As Range) As Boolean    
    If IsNumeric(r2.Value) Then
        Eval = Evaluate(r1 & "=" & r2.Text)
    Else
        Eval = Evaluate(r1 & r2)
    End If
End Function

Assuming user enter 1000 in H2 (to get the machines whose speed are equal to 1000)

A B C D E F G H I J

Description	Asset #	Site	Product Type	Speed	Comments		Speed		Formula
Machine A	1234	A	375 500	1500 500	xxxxxxxxxx		1000		FALSE
Machine B	2345	A	500 1000	1000 800	xxxxxxxxx
Machine C	3456	B	1250	500	xxxxxxxxxx
Machine D	7890	B	375	1000	xxxxxxx

<tbody>
</tbody>

***Notice the values in column E***

Formula in I2
=OR(EVAL(LEFT(E2,SEARCH(" ",E2&" ")),$H$2),IF(ISNUMBER(SEARCH(" ",E2)),EVAL(MID(E2,SEARCH(" ",E2),99),$H$2),FALSE))

Then filter Criteria Range J1:J2 and you get

Description	Asset #	Site	Product Type	Speed	Comments
Machine B	2345	A	500 1000	1000 800	xxxxxxxxx
Machine D	7890	B	375	1000	xxxxxxx

<tbody>
</tbody>

Clear the filter and change H2 to >=1000

Filter (Criteria range always J1:J2) and you get

Description	Asset #	Site	Product Type	Speed	Comments
Machine A	1234	A	375 500	1500 500	xxxxxxxxxx
Machine B	2345	A	500 1000	1000 800	xxxxxxxxx
Machine D	7890	B	375	1000	xxxxxxx

<tbody>
</tbody>

Clear the filter and change H2 to <600

Filter and you get

Description	Asset #	Site	Product Type	Speed	Comments
Machine A	1234	A	375 500	1500 500	xxxxxxxxxx
Machine C	3456	B	1250	500	xxxxxxxxxx

<tbody>
</tbody>

Hope this is what you need

M.

 

[RyanF]

At first glance, and if I understand the underlying logic in the I2 formula, I believe that will work. Marcelo you are a legend.

Now to adapt it to my primary sheet with over 8000 machines...

 

[Marcelo Branco]

At first glance, and if I understand the underlying logic in the I2 formula, I believe that will work. Marcelo you are a legend.

Now to adapt it to my primary sheet with over 8000 machines...

The logic in I2 formula is not complicated.

1st part: EVAL(LEFT(E2,SEARCH(" ",E2&" ")),$H$2)
extracts the left portion of the string in E2. I added a space, E2&" ", to avoid errors when there is just one number in column E

Pass the extracted string together with H2 (the user entry) as parameters to Eval function that does the comparison, using the VBA Evaluate function, returning either True or False.

2nd part: IF(ISNUMBER(SEARCH(" ",E2)),EVAL(MID(E2,SEARCH(" ",E2),99),$H$2),FALSE))
checks if there is a space in E2;
if True extracts the right portion of the string and pass together with H2 to Eval function that evaluates the comparisoon; Else (ie no spaces in E, just a number) returns False.

At last, the OR function returns True if at least one of the comparisons return True.

That's it

M.
ps: not sure about the performance with 8000 machines. Test and tell me.

 

[Marcelo Branco]

oops...
The formula is not in I2, it's in J2

 

[RyanF]

oops...
The formula is not in I2, it's in J2

I think we both know which one you meant haha. I probably won't get back to this job until late next week to test performance over 8000 rows but thanks for your help regardless

 

[Marcelo Branco]

I think we both know which one you meant haha.

Yes, yes, it was just for the records.

I probably won't get back to this job until late next week to test performance over 8000 rows but thanks for your help regardless

You are welcome and good luck!

M.