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]

just noticed the last table in my post above was missing Machine D, but yeah - hopefully this is clear enough

 

[RyanF]

el bumpo (hoping thread bumping is allowed)

 

[Marcelo Branco]

Maybe something like this

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

A B C D E F G H I J

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

<tbody>
</tbody>

Enter a speed (reference value) in H2

Formula in J2
=AND(LEFT($E2,SEARCH(" ",$E2))+0>=$H$2,$H$2>=MID($E2,SEARCH(" ",$E2),99)+0)

Use Advanced Filter
Criteria Range: J1:J2

you get

Description	Asset #	Site	Product Type	Speed	Comments
Machine D	7890	B	375	1000 0	xxxxxxx

<tbody>
</tbody>

M.

 

[RyanF]

Hi Marcelo. I'm not sure the AND formula will give me the functionality that I was after in the filter but you've given me some inspiration from the LEFT/MID/SEARCH functions in your example.

Thanks

 

[Marcelo Branco]

Hi Marcelo. I'm not sure the AND formula will give me the functionality that I was after in the filter but you've given me some inspiration from the LEFT/MID/SEARCH functions in your example.

Thanks

What exactly are you looking for? The formula worked perfectly for me.

M.

 

[RyanF]

What exactly are you looking for? The formula worked perfectly for me.

M.

Oops!
I made a mistake in testing your example - I see how it works now and it's more efficient than my original method.

Unfortunately I can't see how to adapt this so that if I were to search for Speed (in your example: ReferenceValue) with the criteria >=1000 I would get Machines A, B and D as a result

 

[Marcelo Branco]

Unfortunately I can't see how to adapt this so that if I were to search for Speed (in your example: ReferenceValue) with the criteria >=1000 I would get Machines A, B and D as a result

This is what i get using my formula in J2 and typing 1000 in H2
Advanced Filter ---> Criteria Range J1:J2

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 0	xxxxxxx

<tbody>
</tbody>

Is it ok?

M.

 

[RyanF]

Your method does find speeds greater than 1000 without the ">=" operators being entered - thanks.


What if (and I appreciate this is asking for a lot), I wanted exactly 1000? Lets say Machine C has speeds of:
1500 500
The AND statement will return TRUE for Machine C even through it can't do 1000.

I feel this is relevant to my list because each product type is associated with one speed only
Sorry it's taking me a bit to articulate this - has been a long week.

 

[Marcelo Branco]

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.