I have the following data:
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.