Advanced Filter for 3 Criteria

[gsheppar]

I'm trying to use the advanced filter to find phone #'s that show up in the table under fiscalyear 2011, 2010, and 2009, but it only filters if I put a phone number in. How can I set this up so that it finds all phone numbers that were used in 2009, 2010, and 2011 only (A phone number should only show up if it shows up in 2011, 2010, and 2009)?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1							Phone	fiscalyear
2								2009
3								2010
4								2011
5	OrderSource	fiscalyear	FiscalWeekOfYear	FiscalWeekStartDate	OrderNo	InitialRevenue	Quantity	DeletedOrder	DeletedProductRevenue	DeletedQuantity	ShippedOrder	ShippedOrder	ShippedQuantity	BusinessName	ParentCompany	StoreName	Phone
6	CallCenter	2011	23	7/2/2011	60629451	93.2	2		0		60629451	93.2	2	Invalid#TemporaryNumber	Invalid	N/A
7	CallCenter	2011	23	7/2/2011	60688138	101.97	3		0		60688138	101.97	3	Invalid#TemporaryNumber	Invalid	N/A	1111111111
8	Web	2011	23	7/2/2011	60703961	164.99	2		0		60703961	164.99	2	Invalid#TemporaryNumber	Invalid	N/A	1111111111
Sheet2

 

[GlennUK]

A phone number should only show up if it shows up in 2011, 2010, and 2009

... that's not a filtering type of request. That's more like what people do with SQL ... maybe one of the SQL experts out there can construct a query that does this ( and you could run it through MS Query on your data ).

 

[Bradium]

At G2 G3 and G4 use >0
This assumes that the phone number is a number.

Regards

Brad

There is no need to use the = sign before the year. just 2009 etc

 

[GlennUK]

At G2 G3 and G4 use >0
This assumes that the phone number is a number.

Regards

Brad

There is no need to use the = sign before the year. just 2009 etc

That'll give any phone numbers in those years ... I believe the OP wants to see only numbers that occur in all years.

 

[gsheppar]

Thanks for the replies, I was thinking about that last night and was thinking the same thing that it isn't really a filtering question. I ended up just setting up a table with some COUNTIFS and got the values, and then put the unique items into a new table. Thanks for checking and your recommendations though.

 

[Bradium]

Hi Guys

Here is a possible solution with advanced filter


Sub MyFilter()

Range("C10:G25").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"E2:E3"), CopyToRange:=Range("I10"), Unique:=True
Range("C10").Select

End Sub


Regards

Brad

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1			Change years as required		Criteria is E2:E3
2			Fiscalyear
3			2009		FALSE
4			2010
5			2011
6
7
8			Table						Answer
9
10			Fiscalyear	InitialRevenue	Quantity	DeletedOrder	Phone		Phone
11			2008				1		4
12			2009				4		3
13			2010				5
14			2011				1
15			2008				2
16			2009				2
17			2010				4
18			2011				5
19			2011				3
20			2009				4
21			2010				3
22			2008				5
23			2011				4
24			2008
25			2009				3
26
27
Filter

 

[GlennUK]

Hey Brad ... I like it!

 

[Bradium]

Thanks Glenn

I have posted the example file here for those who would like to review the technique.

Regards

Brad

http://www.4shared.com/document/O_eDEI4C/Filter_Unique_Phone_Numbers_by.html

 

[gsheppar]

Brad,
Great solution. Thanks so much for sharing it.