Filter by Class

**flebber** · 08-21-2010, 12:06 AM

This thread relates to this http://www.excelforum.com/excel-prog...ed-filter.html thread I started.

I started this new thread for clarity.

Attached is a sample worksheet. I have made it small to be practical.

Essentially what my intention was is to filter the data on "Datasheet" by class. Count the number of members in each class(used to work out averages) and create a table for each class of final results variation from average etc on another sheet. In this workbook that sheet is call "SampleOut".

Any feedback ideas on the best way to acheive this is appreciated.

Thanks

Sayth

**flebber** · 08-21-2010, 08:15 AM

I am only at the start with this trying to get the bsics right. Any opinion on the direction I am heading with this code.

Sub Fomulas()

With Worksheets("Datasheet")

Dim ClassCount As Integer
Dim Anum, Bnum, Cnum, Dnum, Fnum As Long
Dim Aavg, Bavg, Cavg, Davg As Long

'       Filter by column B(Class id)
        .Range("B").AutoFilter Field:=1, Criteria1:="=1", Operator:=xlAnd
'        Using minus 1 to ensure headers aren't counted in Row Count 
        ClassCount = Cells(Rows.Count, "B").End(xlUp).Row - 1

FinalRow = Worksheets("Datasheet").Range(Cells("E:E"), Cells(LastRow, 5)).Cells
' Find the avg of all values in column and divide by number members
Aavg = (Sum("E2:E & FinalRow")) / ClassCount
' Find the difference for each member between their score and the average
Anum = ("E2:E & FinalRow") - Aavg
' Output values to sheet3

End With
End Sub