Average last 5 exact matches if Condition is met in other column

[cipirica]

Hello everybody. I need help badly from an excel expert. I need a vba to average the last 5 matches in a column. Example:
A. B. C
1 BILL. 9
2 ANDY. 7
3 BILL. 4
4 ANDY. 0
5 BILL. 6
6 ANDY. 2
7 ANDY. 10
8 BILL. 5
9 BILL. 7
10ANDY
11BILL
So, in column A a have names and in B numbers. Starting with row 10 for example, in column C 10 I want the average of the previous five Andy from column B, in C11 the average of previous 5 Bill from column B and so on until the last available cell in column A. I do not need a formula becouse I have 100.000 rows and a formula to give me what I want it will take for ever. I think a vba would be faster. Thank you in advance and I hope someone will spend some of his/hers precious time to help me.

 

[gargamalebarbosa]

Sorry. I get the #name? error

Ok. It is working for me. But I'm from Turkiye and we don't use same setup in MS office. Maybe problem is seperators between { } symbols. If you cant solve problem i can share file with you. But I don't know forum accept that ?
Could you send screen with error message and formula ?

 

[Fluff]

@gargamalebarbosa it would help if you could post your actual formula to the board, rather than just an image that then needs to be written out again.

 

[cipirica]

Sorry. I get the #name? error

Sorry. The formula is working but i need a vba becouse i have 100.000 rows and a formula would take forever. thx

 

[Kaper]

My proposition of the code. As you mentioned 100000 records, I tried to do all the calculations in the computer memory.

The average is calculated from only 5 most recent values. If there were less than 5 values before, average is calculated from all available so far values for given person
I am not including current value in the average (but may be I am wrong? and shall include current value? Then with the first occurence the code could already calculate average from this one?)
Anyway: try the code

Sub test()
Dim inparr As Variant, dict As Object, prev As Variant
Dim lr As Long, i As Long, j As Long, tmp As Double, recent As String
Set dict = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, "A").End(xlUp).Row
inparr = Range("A1:C" & lr).Value
For i = 1 To lr
  If dict.Exists(inparr(i, 1)) Then
    recent = dict(inparr(i, 1))
    prev = Split(recent, ";")
    tmp = 0
    For j = LBound(prev) To UBound(prev)
      tmp = tmp + CDbl(prev(j))
    Next j
    inparr(i, 3) = tmp / (UBound(prev) + 1)
    If UBound(prev) = 4 Then recent = Mid(recent, InStr(recent, ";") + 1)
    dict(inparr(i, 1)) = recent & ";" & CStr(inparr(i, 2))
  Else
    dict(inparr(i, 1)) = CStr(inparr(i, 2))
    inparr(i, 3) = "N/A"
  End If
Next i
For i = 1 To WorksheetFunction.Min(10, lr)
  inparr(i, 3) = ""
Next i
Range("A1:C" & lr).Value = inparr
End Sub

 

[cipirica]

My proposition of the code. As you mentioned 100000 records, I tried to do all the calculations in the computer memory.

The average is calculated from only 5 most recent values. If there were less than 5 values before, average is calculated from all available so far values for given person
I am not including current value in the average (but may be I am wrong? and shall include current value? Then with the first occurence the code could already calculate average from this one?)
Anyway: try the code

Sub test()
Dim inparr As Variant, dict As Object, prev As Variant
Dim lr As Long, i As Long, j As Long, tmp As Double, recent As String
Set dict = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, "A").End(xlUp).Row
inparr = Range("A1:C" & lr).Value
For i = 1 To lr
  If dict.Exists(inparr(i, 1)) Then
    recent = dict(inparr(i, 1))
    prev = Split(recent, ";")
    tmp = 0
    For j = LBound(prev) To UBound(prev)
      tmp = tmp + CDbl(prev(j))
    Next j
    inparr(i, 3) = tmp / (UBound(prev) + 1)
    If UBound(prev) = 4 Then recent = Mid(recent, InStr(recent, ";") + 1)
    dict(inparr(i, 1)) = recent & ";" & CStr(inparr(i, 2))
  Else
    dict(inparr(i, 1)) = CStr(inparr(i, 2))
    inparr(i, 3) = "N/A"
  End If
Next i
For i = 1 To WorksheetFunction.Min(10, lr)
  inparr(i, 3) = ""
Next i
Range("A1:C" & lr).Value = inparr
End Sub

Thx Kaper for your time. It work's like magic. Next time you are in Romania the drinks are on me.​

 

[kvsrinivasamurthy]

As per your data

Sub GetAverage()
Dim A, Res
Dim T As Long, X As Long
Dim Cri As String
Dim B(1 To 5)

Cri = "ANDY"
A = Range(Range("A1").currentregion
For T = UBound(A, 1) To 1 Step -1
If A(T, 1) = Cri Then: X = X + 1: B(X) = A(T, 2)
If X = 5 Then Exit For
Next T
Res = WorksheetFunction.Average(B)
[F2] = Res
End Sub

 

[cipirica]

Thx Kaper for your time. It work's like magic. Next time you are in Romania the drinks are on me.​

Hi Kaper. Me again. You vba code work's perfectly but i encounter a problem when using decimals. For example when i have BILL. 9,37 it gives me an error. It does not seem to recognise the number as decimals. Can you help me ? THX

 

[Kaper]

Probably the reason is because you use non-american decimal place sign.
Try such modification (the most probable one):

      tmp = tmp + CDbl(Replace(prev(j), ".", ","))

But it's not working try next one.

      tmp = tmp + CDbl(Replace(prev(j), ",", "."))

If still there is a problem it could be because some other signs (like spabe, non-printable-space or currency symbol in the cell - are you sure there are just and only numbers?


And standard comment for reporting errors in VBA code on any forum: "it gives me an error" is rather general/vague information.
What is the error message? When the code stops, you have the option to choose Debug. Which code line is highlighted in yellow when you enter debugging mode after an error?

 

[cipirica]

Thx. I will give it a try and let you know how it work's

Probably the reason is because you use non-american decimal place sign.
Try such modification (the most probable one):

      tmp = tmp + CDbl(Replace(prev(j), ".", ","))

But it's not working try next one.

      tmp = tmp + CDbl(Replace(prev(j), ",", "."))

If still there is a problem it could be because some other signs (like spabe, non-printable-space or currency symbol in the cell - are you sure there are just and only numbers?


And standard comment for reporting errors in VBA code on any forum: "it gives me an error" is rather general/vague information.
What is the error message? When the code stops, you have the option to choose Debug. Which code line is highlighted in yellow when you enter debugging mode after an error?

 

[cipirica]

Probably the reason is because you use non-american decimal place sign.
Try such modification (the most probable one):

      tmp = tmp + CDbl(Replace(prev(j), ".", ","))

But it's not working try next one.

      tmp = tmp + CDbl(Replace(prev(j), ",", "."))

If still there is a problem it could be because some other signs (like spabe, non-printable-space or currency symbol in the cell - are you sure there are just and only numbers?


And standard comment for reporting errors in VBA code on any forum: "it gives me an error" is rather general/vague information.
What is the error message? When the code stops, you have the option to choose Debug. Which code line is highlighted in yellow when you enter debugging mode after an error?

THx again. It works beautufuly. THX for your time