Looking for the best ideas on how to do my filter using multiple criteria's

[tonywatsonhelp]

Hi Everyone,

OK before we start I'd just like to ask everyone not to suggest a pivot table and slicers for this solution,
to explain why, my table of data is quite large, about A1:GZ8000 and for reasons I can not explain, when i convert my data form a sheet to a table the entire document slows down and it freezes up and I cant get it to do anything, even crashes on me, it very strange and after spending two days trying to work out why I've come to the conclusion its just a glitch, i have to find another way!

so what I'm trying to do is have the option to filter buy more than just one criteria,

So My data is sheet data range A1:GZ8000
Location is column D
Type is column F

once its filtered i can do the rest,

but i need amacro or a formula to filter it like this,

so
results go in sheet Results A20:GZ and last row

above in B2 I have Location,
B3 is a drop down box so i choose "London"
but I want B4 to also be a dropdown, and if I select "Leigh" ii get results for london and leigh, and so on lets say up to 5 selections,
then in C I'd like to do another say "Type" and I can select company type,
and maybe add more?

if anyone has anyideas how i could do this, if its even possible then your help would be greatly aperesiated

Thanks

Tony

 

[BSALV]

luke777.xlsb
	GZ	HA	HB	HC	HD	HE	HF	HG	HH	HI	HJ	HK	HL
1			location	select	Berlin		type	select	type1		MyFilter
2			Berlin	1	London		type1	1	type3		1
3			Brussels				type2		type5		0
4			London	1			type3	1			1
5			Paris				type4				1
6			0				type5	1			0
7							0				1
8											0
9											1
10											0
11											0
12											0
13											0
14											0
15											0
16											0
17											0
18											0
19											0
20											0
21											0
22											0
23											0
Blad3

Cell Formulas
Range	Formula
HD1:HD2	HD1	=FILTER(HB2:HB37,HC2:HC37=1)
HH1:HH3	HH1	=FILTER(HF2:HF38,HG2:HG38=1)
HB2:HB6	HB2	=SORT(UNIQUE(D2:D8000))
HF2:HF7	HF2	=SORT(UNIQUE(F2:F8000))
Dynamic array formulas.

		location	select	Berlin		type	select	type1		MyFilter
		Berlin	1	London		type1	1	type3		1
		Brussels				type2		type5		0
		London	1			type3	1			1
		Paris				type4				1
		0				type5	1			0
						0				1
										0
										1
										0
										0
										0
										0
										0
										0
										0
										0
										0
										0
										0
										0
										0
										0

 

[BSALV]

there are 3 named ranges to be defined (see in macro) : location, type and myfilter
In HB and HG you find the unique locations and types.
To select/unselect them set or delete a "1" in the next cell
Run this macro (principe : filter on 1 column HJ

Sub Mijnfilter()
     Dim fl()
     t = Timer
     With Sheets("blad3")                                       'naam of your sheet
          If .AutoFilterMode Then .AutoFilterMode = False       'no autofilter
          With .UsedRange
               lastrow = .Row + .Rows.Count - 2                 'find last rownumber
          End With

          With Range("Location").Offset(Rows.Count - 1).End(xlUp)     '----> range HD1 = named range "location"
               myloc = Application.Transpose(.Offset(1 - .Row).Resize(.Row).Value)     '---> all the wanted locations (with a 1 in column HC)
          End With

          With Range("type").Offset(Rows.Count - 1).End(xlUp)   '---> range HH1 = named range "type
               mytype = Application.Transpose(.Offset(1 - .Row).Resize(.Row).Value)     '---> all the wanted types (with a 1 in column HG
          End With

          a = .Range("D2").Resize(lastrow, 3).Value             'array with contents of columns D:F
          ReDim fl(1 To UBound(a))                              'resize an array with equal rows
          For i = 1 To UBound(a)                                'loop through data
               fl(i) = -(IsNumeric(Application.Match(a(i, 1), myloc, 0)) + IsNumeric(Application.Match(a(i, 3), mytype, 0)) = -2)     'matching location and matching type = 1
          Next

          With Range("MyFilter").Resize(1 + UBound(fl))         '--> named range "MyFilter" in range HJ1
               .Offset(1).Resize(UBound(fl)).Value = Application.Transpose(fl)     'write your array to HJ2
               .AutoFilter 1, 1                                 'autofilter on that column alone on value 1
          End With

     End With
     MsgBox Timer - t
End Sub