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
Bookmarks