PDA

View Full Version : NEED HELP! Average Duplicates and Print them separately



redsun001
10-30-2017, 12:14 PM
Hi all,

I am new to Excel VBA and I have this problem which I need help with...

Basically if I have a columns of data such as:


X values
Y values


1
5


1
6


2
8


9
16


12
21



As you can see; for values of x=(1) I have two different y values =( 5 and 6). I need to take an average of both y values for (repeated x values) and print the entire data with average of different y values for (repeated x value).
Example output:


X values
Y values


1
5.5


2
8


9
16


12
21


Can someone please help me with this as I have spent hours trying to do this :banghead: and can't simply write a proper loop for this. I will really appreciate it if you can show me how it is done.

Thanks

6StringJazzer
10-30-2017, 05:02 PM
Put this code in the module for the sheet containing the data. If your data is in A:B, the output will be in D:E.


Private Sub Averages()


Dim ReadRow As Long ' row
Dim WriteRow As Long

ReadRow = 2
WriteRow = 2

Do Until Cells(ReadRow, "A") = ""

If Application.WorksheetFunction.CountIf(Range(Cells(2, "A"), Cells(ReadRow, "A")), Cells(ReadRow, "A")) = 1 Then
Cells(WriteRow, "D") = Cells(ReadRow, "A")
Cells(WriteRow, "E") = Application.WorksheetFunction.AverageIf(Range("A:A"), Cells(ReadRow, "A"), Range("B:B"))
WriteRow = WriteRow + 1
End If

ReadRow = ReadRow + 1

Loop


End Sub

greyangel
11-01-2017, 06:23 AM
Can't you just make a pivot table for this information? No need for a macro.