Count of Occurences From First Occurence

[holzie]

I am sure this might be an easy one as I refer to this site often and helps me out a lot!

I have large data that comes in with number of occurrences by date. What I am trying to do is try to find trends faster by counting the number of occurrences within the first 30 days of first occurrence. For example if the first occurrence happened Sep 1, and there were 3 more in September I would like to have this output for this particular Item in a separate column. See below for example of how I would like it to look. The first table is an example of data that I look at. The next table would be a separate table of a report I would like to see out of the data. Any ideas on how to create something like this?

Item	Occurrence Date
A	7/11/2017
A	7/14/2017
A	7/17/2017
A	8/25/2017
A	8/27/2017
B	1/2/2017
B	2/5/2017
B	3/6/2017
B	4/9/2017
B	5/15/2017
C	3/6/2017
C	3/8/2017
C	3/9/2017
C	3/10/2017
C	3/12/2017

<tbody>
</tbody><colgroup><col><col></colgroup>

Item First 30 Days Since First Appearance First 60 Days Since First Appearance First 90 Days Since First Appearance A 3 5 B 1 2 2 C 5 <tbody> </tbody><colgroup><col><col><col><col></colgroup>

<tbody>
</tbody>

 

[oldbrewer]

Item	Occurrence Date
A	01/Jun/2017
A	10/Jun/2017
A	19/Jun/2017
A	28/Jun/2017
A	07/Jul/2017
B	01/Jul/2017
B	29/Jul/2017
B	03/Aug/2017
B	12/Aug/2017
B	21/Sep/2017
C	01/Apr/2017
C	10/Jun/2017
C	10/Jul/2017
C	26/Sep/2017
C	05/Oct/2017
Item	First 30 Days Since First Appearance	First 60 Days Since First Appearance	First 90 Days Since First Appearance
A	3	4	4
B	1	3	4
C	0	0	1
		days	days	days
	first appearance	30	60	90
A	01/Jun/2017	3	4	4
B	01/Jul/2017	1	3	4
C	01/Apr/2017	0	0	1
		formula for 3 (under 30)
		SUMPRODUCT(($A$2:$A$16=$A28)*($B$2:$B$16>$B28)*($B$2:$B$16<=$B28+C$27))
		formula for 01/Jun/2017 (under first appearance)
		=OFFSET($A$1,MATCH(A28,$A$2:$A$16,0),1)

<colgroup><col><col><col><col><col span="8"></colgroup><tbody>
</tbody>

 

[holzie]

Impressive! Since I have a mass data stream it would take a long time using the match formula or am I missing something. Attached is just a short piece of data that I am looking at. To use your formula, I believe I would have to manually enter this in for each Item number? Or how would you approach this?

https://www.dropbox.com/s/pfjs8l73l4lf8cm/Sample.xlsx?dl=0

 

[oldbrewer]

Try it on 5000 rows - how long does it take ? (I never download or use drop box)

if it takes too long I would switch to a macro approach

 

[holzie]

I tried the formula but did not work on the spreadsheet I posted. Is there another way to post a spreadsheet?

 

[Peter_SSs]

If you have very large data, you might like to give this macro a try in a copy of your workbook.
I have assumed ..
- Items in column A, Dates in column B and results can go in columns D, E, F, ...
- By "first occurrence" you mean the earliest date, not necessarily the first date you come to in column A
- You want to count within 30, 60 and 90 days of the first occurrence. Those values can be altered/reduced/added to in the code at the first commented line.

Note that my date format is d/mm/yyyy. Adjust the other commented line to match your preferred date format.

Sub CountEm()
  Dim dMin As Object, dRow As Object
  Dim a As Variant, b As Variant, aDays As Variant
  Dim i As Long, j As Long
  
  aDays = Array(30, 60, 90) '<- The 'day periods' you want to count
  
  ReDim Preserve aDays(1 To UBound(aDays) - LBound(aDays) + 1)
  Set dMin = CreateObject("Scripting.Dictionary")
  Set dRow = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Formula
  For i = 1 To UBound(a)
    If dMin.exists(a(i, 1)) Then
      If a(i, 2) < dMin(a(i, 1)) Then
        dMin(a(i, 1)) = a(i, 2)
      End If
    Else
      dMin(a(i, 1)) = a(i, 2)
      dRow(a(i, 1)) = dMin.Count
    End If
  Next i
  With Range("D1:E1")
    .Value = Array("Item", "First")
    With .Offset(1).Resize(dMin.Count)
      .NumberFormat = "d/mm/yyyy"   '<- Adjust this to suit your preferred date format
      .Value = Application.Transpose(Array(dMin.keys, dMin.items))
    End With
  End With
  ReDim b(1 To dMin.Count, 1 To UBound(aDays))
  For i = 1 To UBound(a)
    For j = 1 To UBound(aDays)
      If a(i, 2) - dMin(a(i, 1)) <= aDays(j) Then
        b(dRow(a(i, 1)), j) = b(dRow(a(i, 1)), j) + 1
      End If
    Next j
  Next i
  With Range("F1").Resize(, UBound(b, 2))
    .Value = aDays
    .Offset(1).Resize(dMin.Count).Value = b
  End With
End Sub

My results from your sample data in post 1. They don't agree with yours so if mine are wrong, please explain in more detail why.

Book1
	A	B	C	D	E	F	G	H
1	Item	Occurrence Date		Item	First	30	60	90
2	A	11/07/2017		A	11/07/2017	3	5	5
3	A	14/07/2017		B	2/01/2017	1	2	3
4	A	17/07/2017		C	6/03/2017	5	5	5
5	A	25/08/2017
6	A	27/08/2017
7	B	2/01/2017
8	B	5/02/2017
9	B	6/03/2017
10	B	9/04/2017
11	B	15/05/2017
12	C	6/03/2017
13	C	8/03/2017
14	C	9/03/2017
15	C	10/03/2017
16	C	12/03/2017
Sheet5

 

[MickG]

Try this for results starting "D1".
NB:- If the results are not as expected, please supply a small example of data showing the correct, expected results.

[COLOR="Navy"]Sub[/COLOR] MG11Oct32
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] dif [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dt [COLOR="Navy"]As[/COLOR] Date, Q [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, Al [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
c = 1
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        ReDim ray(1 To 1)
        ray(1) = Dn.Offset(, 1).Value
        .Add Dn.Value, ray
    [COLOR="Navy"]Else[/COLOR]
        Q = .Item(Dn.Value)
        ReDim Preserve Q(1 To UBound(Q) + 1)
        Q(UBound(Q)) = Dn.Offset(, 1).Value
        .Item(Dn.Value) = Q
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Columns("D:H").ClearContents
Range("D1").Resize(, 5).Value = Array("Item", "Start Date", "First 30 Days Since First Appearance", "First 60 Days Since First Appearance", "First 90 Days Since First Appearance ")
[COLOR="Navy"]Set[/COLOR] Al = CreateObject("System.Collections.ArrayList")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    [COLOR="Navy"]For[/COLOR] n = 1 To UBound(.Item(K))
        [COLOR="Navy"]If[/COLOR] Not Al.Contains(.Item(K)(n)) [COLOR="Navy"]Then[/COLOR] Al.Add .Item(K)(n)
    [COLOR="Navy"]Next[/COLOR] n
    Al.Sort: .Item(K) = Al.ToArray
    Dt = .Item(K)(0)
    c = c + 1
    Cells(c, 4) = K
    Cells(c, 5) = Dt
    [COLOR="Navy"]For[/COLOR] n = 1 To UBound(.Item(K))
            dif = DateDiff("d", Dt, .Item(K)(n))
            [COLOR="Navy"]If[/COLOR] dif <= 30 [COLOR="Navy"]Then[/COLOR] Cells(c, 6) = Cells(c, 6) + IIf(n = 1, 2, 1)
            [COLOR="Navy"]If[/COLOR] dif <= 60 [COLOR="Navy"]Then[/COLOR] Cells(c, 7) = Cells(c, 7) + IIf(n = 1, 2, 1)
            [COLOR="Navy"]If[/COLOR] dif <= 90 [COLOR="Navy"]Then[/COLOR] Cells(c, 8) = Cells(c, 8) + IIf(n = 1, 2, 1)
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[holzie]

Thanks, I was able to make it work in the workbook I attached in the dropbox earlier but for some reason the Item number did not come through correctly. Came back as a date. Any thoughts?

Item	Occurrence Date		Item	First	30	60	90
38384	9/13/2017		02/01/2005	06/21/2017	2	2	5
45646	7/20/2017		12/20/2024	07/11/2017	4	5	6
11720	9/14/2017		02/01/1932	06/05/2017	6	11	23
44639	9/21/2017		03/19/2022	02/07/2017	2	2	2
26553	9/15/2017		09/11/1972	09/15/2017	1	1	1
11732	7/3/2017		02/13/1932	04/13/2017	1	1	3
76577	9/25/2017		08/28/2109	09/25/2017	2	2	2
11720	9/27/2017		11/28/2073	06/26/2017	1	1	1

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col span="2"></colgroup>

 

[MickG]

Try this:-
Here is the returned file :-
https://app.box.com/s/93kjv9tnzr8om6n2lzptmn3jo2kpj145

 

[holzie]

Getting closer I think. The item numbers are showing up but there must be something in the code that isn't working properly. For example take a look at item number 44573 has had only one occurrence in the data on 12/19/2017 but it shows it had 10 occurrences in the first 30 days 16 in 60 days and 24 in 90 days. There are a number of items that have the same cadence for some reason.