excel - Average column value based on other column value vba code

Question

I have following data ( just few are shown)

I need to get the average value for similar groups in column 3. There are many names in column 1( not shown all) and these names are different time to time, therefore I cant specify name in the command line as it is not convenient. Can you please help me to put a vba code to solve this problem. Thanks.

Column 1  Column 2       
123A        1245
123A        3400
123A        1200
124A        1201
124A        1001
111B        900
123B        800

Answer 1

This will create a list to the side:

Sub dupremoveavg()
Dim ws As Worksheet
Dim lastrow As Long

Set ws = Sheets("Sheet13") ' Change to your sheet

With ws
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    With .Range("A1:B" & lastrow)
        .Offset(, 4).Value = .Value
    End With
    With .Range("B2:B" & lastrow)
        .Offset(, 4).FormulaR1C1 = "=AVERAGEIF(C1,RC1,C[-4])"
        .Offset(, 4).Value = .Offset(, 4).Value
    End With

    .Range("E1:F" & lastrow).RemoveDuplicates 1, xlYes

End With

End Sub

---

Or for non vba you can use AVERAGEIF()

=AVERAGEIF(A:A,A2,B:B)

---

EDIT

As per your edits.

Use this formula in C2 and copy down.

=IF(A2<>A1,AVERAGEIF(A2:INDEX($A2:$A$20000,MATCH(TRUE,(INDEX($A2:$A$20000<>A2,)),0)),A2,B2:INDEX($B2:$B$20000,MATCH(TRUE,(INDEX($A2:$A$20000<>A2,)),0))),"")

This formula will reference a dynamic range, in that it will alwyas grow or shrink to accommodate the changing number of rows.

=IF(A2<>A1,AVERAGEIF(A2:INDEX($A2:INDEX(A:A,MATCH(1E+99,A:A)+1),MATCH(TRUE,(INDEX($A2:INDEX(A:A,MATCH(1E+99,A:A)+1)<>A2,)),0)),A2,B2:INDEX($B2:INDEX(B:B,MATCH(1E+99,A:A)+1),MATCH(TRUE,(INDEX($A2:INDEX(A:A,MATCH(1E+99,A:A)+1)<>A2,)),0))),"")