(Solved) – Counting number of instances according to date difference (Excel/VBA)


I have the following data:

Excel Table Screen Capture

That is:

Acquirer ID ||| Target ID (not useful) ||| Date

123 ||| 456 ||| 20/01/2020

789 ||| 910 ||| 28/02/2019

I would like an additional column where for each acquirer I have a count of how many acquisitions it made in the past 3 years.
Therefore, for each acquirer ID with a given date, I need to count how many times that ID appears with a date less than 3 years distant from that date.

Thus if an acquirer made an acquisition in 2020, 2016, 2015 and 2014, in each instance, the count will amount to 0, 2, 1, 0 respectively for that given acquirer.

I have tried with COUNTIFS(), IF (), VLOOKUP(), but I could not get the result wanted as, once the condition is met, it sums all the IDs irrespective of the dates.

I also tried writing a Macro on VBA but my knowledge of the language is basically 0. I post the code in case it gives a better idea of the problem

Dim Sheet As Worksheet

Set dbs = ThisWorkbook.Sheets("db")

Dim i As Integer, j As Integer, count As Integer

lr = dbs.Cells(Rows.count, 1).End(xlUp).Row 
x = 0

For i = 2 To lr
    For j = i   1 To lr
    Set myrange = Range("i:lr")
        If dbs.Cells(i, 4).Value - Application.WorksheetFunction.VLookup(dbs.Cells(i, 4).Value, myrange, 4, False) < 3 Then
        'I get a bug with Application.WorksheetFunction.VLookup, it might be that an error must be dealt with in case of missing values
        count = count   1
        End If
    Next j
Next i

dbs.Cells(i, 5).Value = count

End Sub

Any help is really appreciated, thank you in advance.

Leave a Reply

Your email address will not be published. Required fields are marked *