VBA - Averaging columns between blank rows by referencing another column

[1234rhi]

Hi there,

I have a really large data file >20000 rows, and I would like to have averages for several columns depending on a person's ID number; however, each person has a different number of rows for each condition, and I'm not sure how to average the array if it's a variable number of rows.

It sounds kind of complicated, but being very new to VBA I'm sure it's just a problem of me not being able to wrap my head around the syntax.

An example of what my data would look like:


A_B__C__D
1 182 362 164
1 313 273
1 350 118 298
1 188 265 109
2 241 124 116
2 376 229 186
2 125 173 176
2 138 351 329
2 288 273 191
3 160 320 189
3 277 314
3 167 313 198

(in my actual file I have ~30 columns though)

And I've started by putting blank rows between subjects (ID numbers) with this code:


Sub Insert_Rows()
Dim lastRow, chkRw As Integer
lastRow = Range("A" & Rows.Count).End(xlUp).Row
For chkRow = lastRow To 2 Step -1
If Range("A" & chkRow) <> Range("A" & chkRow + 1) Then
Range("A" & chkRow + 1).EntireRow.Insert shift = xlDown
End If
Next
End Sub
End Sub


So, ideally what I'd like to do is fill in the blank rows with averages of the above data for each column, for each participant (dependent on the ID column) - the problem is that there's missing data, and a variable number of rows for each person. Following that (if possible!) I'd like to copy those averages onto a separate sheet.

I'm using visual basic, and Open Office Calc 3.2.1/ Excel 2007

Thanks in advance!!!
Rhi

 

[AlphaFrog]

There is the Subtotal feature under the Data menu that is basically designed to do just this. It does averages as well.

It doesn't really need the blank rows in between the accounts . It will insert the averages each time there is a change in the ID Numbers in column A.

• Select all your data in columns A:D
• Select from the menu Data\ Subtotals
  • At each change in: Column A
  • Use function: Average
  • Add subtotal to: Columns B, C, and D
  • OK

This doesn't address copying the averages to another sheet, but it's a start.

 

[AlphaFrog]

To copy the averages to another sheet, you could copy the Unique ID's from column A and then use the AVERAGEIF function to calculate the averages.

Copy Unique IDs:

• Select column A of your data
• Select from the menu; Data\ Filter\ Advanced filter
  • Action: Filter the list in place
  • Unique records only: Checked
  • OK

This will filter all the unique IDs in column A
Select all the visible unique IDs and past them to column A on a blank sheet
Put this formula in B2 on the blank sheet and copy it down and across to calculate the averages for each ID from each column of the data in Sheet1.

=AVERAGEIF(Sheet1!$A:$A,$A2,Sheet1!B:B)

You could of course record these steps to get you started on a macro.

 

[1234rhi]

Thank you both so much! I will try both of those things and let you know which one works best

 

[1234rhi]

Ok, so the =AVERAGEIFS function worked fantastically, and it basically did exactly what I was going for!

Thanks again!!