Dynamic Filter by Date then unique

[Raysoc]

Got a spreadsheet its maxed for data.

I need to filter based on date, but once I have the date I then need the unique num of ID's on that date.

Manually I can auto filter, then do an advanced filter unique only copy and then count... but there must be an easier way as I would prefer just vchanging the date in the manual filter and see the numbers.

thanks!

 

[jbeaucaire]

If the sheet is huge, I would resist array formulas for performance sake.

If you can add a "helper" column to your data sheet that watches your filter cell, this could get easy.

Excel Workbook
	A	B	C	D	E	F
1	Dates	IDs	Helper		Filter	IDs
2	1-Jan	ID#1	1		1-Jan	ID#1
3	2-Jan	ID#2				ID#7
4	3-Jan	ID#3				ID#13
5	4-Jan	ID#4				ID#19
6	5-Jan	ID#5
7	6-Jan	ID#6
8	1-Jan	ID#7	2
9	2-Jan	ID#8
10	3-Jan	ID#9
11	4-Jan	ID#10
12	5-Jan	ID#11
13	6-Jan	ID#12
14	1-Jan	ID#13	3
15	2-Jan	ID#14
16	3-Jan	ID#15
17	4-Jan	ID#16
18	5-Jan	ID#17
19	6-Jan	ID#18
20	1-Jan	ID#19	4
21	2-Jan	ID#20
Sheet1

Just copy those two formulas down as far as needed.

Will that work for you? It has the benefit of being virtually instantaneous even with huge datasets.