Group Numbers and find average

[aheye]

Hi all.

I have a bunch of data such as like below :

A B
1600 0.465479
1600 0.46517
1600 0.465345
1600 0.465609
1600 0.465238
1600 0.46523
1600 0.465233
1600 0.465259
1600 0.465573
1600 0.465054
1601 0.465829
1601 0.465234
1601 0.465543
1601 0.465356
1601 0.465435
1601 0.465553
1601 0.465236
1601 0.465473
1601 0.465245
1601 0.465429
1602 0.465726
1602 0.466014
1602 0.465897
1602 0.465528
1602 0.465673
1602 0.465692
1602 0.465918
1602 0.465958
1602 0.465756
1602 0.465843


how can I group those data and find the average of it.

As column A is the group while the column B is the data.

At the moment I just used '=Median(B1:B10)' ...but I have thousands of data.
Note that for each group, it is in a frequency of 10 data.

Thanks guys.

Aheye

[PingPing]

I can think of 2 possible solutions:

1. (easier solution) use '=SUMIF()' and '=COUNTIF()'; or
2. (better solution) use ADO and SQL.

[111StepsAhead]

Sub test()
    Dim x As Long
    Dim y As Long
    Dim last_Row As Long
    Dim last_Row2 As Long
    Dim temp_String As String
    Dim temp_String2 As String
    Dim sum_Total As Long
    Dim count As Long
    
    sum_Total = 0
    count = 0
    Worksheets("Main").Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("D2"), Unique:=True
    last_Row = Range("A65536").End(xlUp).Row
    
    Range("D1").Value = "Unique Values"
    Range("E1").Value = "Total"
    Range("F1").Value = "Count"
    Range("G1").Value = "Average"
    
    For x = 1 To last_Row
        temp_String = Range("A" & x).Value
        temp_String2 = Range("B" & x).Value
        last_Row2 = Range("D65536").End(xlUp).Row
        For y = 2 To last_Row2
            If Range("D" & y).Value = temp_String Then
                Range("E" & y).Value = Range("E" & y).Value + temp_String2
                Range("F" & y).Value = Range("F" & y).Value + 1
                Range("G" & y).Value = Range("E" & y).Value / Range("F" & y).Value
            Else
                'do nothing
            End If
        Next y
    Next x
End Sub

It is probably fairly inefficient but it will do the job.