Filtering and then counting distinct values

Question

This is for Excel: I've been tasked with counting distinct records after I have filtered the data. I have 330 rows with column A containing the 'name' and in Column B I have the name of a test that was done for each 'name', which each 'name' could have taken several iterations of the same test. The test results are in Column C.

Col A -Student  Col B -Exam          Col C - Grade
Student 1       Exam 1              .80
Student 2       Exam 1              .50
Student 3       Exam 1              .90
Student 2       Exam 1              .75
Student 4       Exam 1              .90
Student 5       Exam 1              .55
Student 2       Exam 2              .90
Student 1       Exam 2              .90
....            ....                ...

If I filter col B for Exam 1, I want to count the unique number of students that have taken Exam 1.

Answer 1

I'm not sure how column B is fitting in here, but...

select distinct column_A, count(*)
from table
where column_C = 'A+'
group by column_A

Answer 2

In line with thursdaysgeek

strFile = Workbooks(1).FullName
''Note HDR=Yes, so column names can be used
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
cn.Open strCon
Set rs = CreateObject("ADODB.Recordset")

strSQL = "SELECT DISTINCT Student, Exam FROM [Sheet4$] " _
       & "WHERE Exam='Exam 1'"

rs.Open strSQL, cn

For i = 0 To rs.Fields.Count - 1
    Sheets("Sheet5").Cells(1, i + 1) = rs.Fields(i).Name
Next

Sheets("Sheet5").Cells(2, 1).CopyFromRecordset rs

Answer 3

I wouldn't use code at all here, I'd go for a Pivot Table

Answer 4

Found this:

=SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000&""))

on the Excel Forum

Tested in on your example and .. it works :-)