Hi

I have a data set which has 6 columns (and lots of rows). Every row is different but I want to aggregate them based on 4 fields and then find the average of the numerical column for the results. Sorry if this is unclear but if you are familiar with Access I basically want to Group based on 4 fields and find the average of the 5th field.

My initial approach was to introduce a column which is a combination of the 4 fields I want to group by, simply in the Excel file (=A2&B2&C2&D2) and then find duplicates of that. I have a solution for this in VBA but when importing new data sets in this method is very slow, so I want to be able to do the whole thing in VBA.

The code which I have so far is:

' collect array from a specific column and print it to a new one without duplicates
'           fromColumn - this is the column you need to remove duplicates from
'           toColumn - this will reprint the array without the duplicates

Sub CollectArray()

    Dim fromColumn As String
    Dim toColumn As String

    fromColumn = "A"
    toColumn = "N"
    
    ReDim arr(0) As String

    Dim i As Long
    For i = 1 To Range(fromColumn & Rows.Count).End(xlUp).Row
        arr(UBound(arr)) = Range(fromColumn & i)
        ReDim Preserve arr(UBound(arr) + 1)
    Next i
    ReDim Preserve arr(UBound(arr) - 1)
    RemoveDuplicate arr
    Range(toColumn & "1:" & toColumn & Range(toColumn & Rows.Count).End(xlUp).Row).ClearContents
    For i = LBound(arr) To UBound(arr)
        Range(toColumn & i + 1) = arr(i)
    Next i
    
End Sub




' sums up values from one column against the other column
' params:
'           fromColumn - this is the column with string to match against
'           toColumn - this is where the SUM will be printed to
'           originalColumn - this is the original column including duplicate
'           valueColumn - this is the column with the values to sum

Private Sub DoSum()

    Dim fromColumn As String
    Dim toColumn As String
    Dim originalColumn As String
    Dim valueColumn As String
    
    fromColumn = "N"
    toColumn = "S"
    originalColumn = "A"
    valueColumn = "F"
    
    Range(toColumn & "1:" & toColumn & Range(toColumn & Rows.Count).End(xlUp).Row).ClearContents
    
    Dim i As Long
    
    For i = 1 To Range(fromColumn & Rows.Count).End(xlUp).Row
        Range(toColumn & i) = WorksheetFunction.SumIf(Range(originalColumn & ":" & originalColumn), Range(fromColumn & i), Range(valueColumn & ":" & valueColumn)) / WorksheetFunction.Countif(Range(originalColumn & ":" & originalColumn), Range(fromColumn & i))
    Next i

End Sub


The first part finds the unique values (of the column which is a concatenation of the 4 columns) and pastes them into column N. The 2nd part then pastes the relevant averages next to them.

All I need to change is to search for unique combinations of the 4 cells, and then make the same change to the 2nd part. I am a novice with VBA, so apologies in this is not too clear.

Any help would be great.

Thanks