ADVANCED FILTER vs FORMULA

[HarrysPrinters]

My spreadsheet consists of 2 list-dependant columns. A = School Name; B = Subject Choice. Data validation consists of ALL SCHOOLS in a given area and ALL SUBJECTS for our Province. Schools can appear in multiple consecutive rows due to various subject choices. I am capturing data as I receive it (hard copy response form). I NOW NEED TO RUN A 'REPORT' TO SEE WHICH SCHOOLS HAVE NOT RESPONDED. Tried the ADVANCED FILTER but this is too tedius as I have to copy unique records, paste them WITH the validation data, remove duplicates (those schools who have responded) and what is left are those schools who have not responded. Is there not a formula-driven method?

 

[shyrath]

I would use no formula or filter but a pivottable.

nice tutorial here:

http://www.edferrero.com/ExcelTutorials/PivotTableTutorial2003/tabid/89/Default.aspx

Excel Workbook
	A	B	C	D	E	F	G	H
1	SchoolName	Subject	Response			PIVOTTABLE
2	School A	English	0			Response	0
3	School B	Math	1
4	School C	Physics	0			Count of Subject
5	School D	English	1			SchoolName	Subject	Total
6	School E	Math	0			School A	English	1
7	School A	Physics	1				Math	1
8	School B	English	0			School A Total		2
9	School C	Math	1			School B	English	1
10	School D	Physics	0			School B Total		1
11	School E	English	1			School C	English	1
12	School A	Math	0				Physics	1
13	School B	Physics	1			School C Total		2
14	School C	English	0			School D	Physics	1
15	School D	Math	1			School D Total		1
16	School E	Physics	0			School E	Math	1
17							Physics	1
18						School E Total		2
19						Grand Total		8
Sheet2