Count of Occurences From First Occurence

holzie

New Member
Joined
Apr 23, 2015
Messages
19
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?

ItemOccurrence Date
A7/11/2017
A7/14/2017
A7/17/2017
A8/25/2017
A8/27/2017
B1/2/2017
B2/5/2017
B3/6/2017
B4/9/2017
B5/15/2017
C3/6/2017
C3/8/2017
C3/9/2017
C3/10/2017
C3/12/2017

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


ItemFirst 30 Days Since First AppearanceFirst 60 Days Since First AppearanceFirst 90 Days Since First Appearance
A35
B122
C5

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

<tbody>
</tbody>
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,985
ItemOccurrence Date
A01/Jun/2017
A10/Jun/2017
A19/Jun/2017
A28/Jun/2017
A07/Jul/2017
B01/Jul/2017
B29/Jul/2017
B03/Aug/2017
B12/Aug/2017
B21/Sep/2017
C01/Apr/2017
C10/Jun/2017
C10/Jul/2017
C26/Sep/2017
C05/Oct/2017
ItemFirst 30 Days Since First AppearanceFirst 60 Days Since First AppearanceFirst 90 Days Since First Appearance
A344
B134
C001
daysdaysdays
first appearance306090
A01/Jun/2017344
B01/Jul/2017134
C01/Apr/2017001
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

New Member
Joined
Apr 23, 2015
Messages
19
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

Board Regular
Joined
Apr 11, 2010
Messages
10,985
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

New Member
Joined
Apr 23, 2015
Messages
19
I tried the formula but did not work on the spreadsheet I posted. Is there another way to post a spreadsheet?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,250
Office Version
365
Platform
Windows
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.

Rich (BB code):
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Item</td><td style="text-align: right;;">Occurrence Date</td><td style="text-align: right;;"></td><td style=";">Item</td><td style="text-align: right;;">First</td><td style="text-align: right;;">30</td><td style="text-align: right;;">60</td><td style="text-align: right;;">90</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">A</td><td style="text-align: right;;">11/07/2017</td><td style="text-align: right;;"></td><td style=";">A</td><td style="text-align: right;;">11/07/2017</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">A</td><td style="text-align: right;;">14/07/2017</td><td style="text-align: right;;"></td><td style=";">B</td><td style="text-align: right;;">2/01/2017</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">A</td><td style="text-align: right;;">17/07/2017</td><td style="text-align: right;;"></td><td style=";">C</td><td style="text-align: right;;">6/03/2017</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">A</td><td style="text-align: right;;">25/08/2017</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">A</td><td style="text-align: right;;">27/08/2017</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">B</td><td style="text-align: right;;">2/01/2017</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">B</td><td style="text-align: right;;">5/02/2017</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">B</td><td style="text-align: right;;">6/03/2017</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">B</td><td style="text-align: right;;">9/04/2017</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">B</td><td style="text-align: right;;">15/05/2017</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">C</td><td style="text-align: right;;">6/03/2017</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">C</td><td style="text-align: right;;">8/03/2017</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">C</td><td style="text-align: right;;">9/03/2017</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">C</td><td style="text-align: right;;">10/03/2017</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">C</td><td style="text-align: right;;">12/03/2017</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet5</p><br /><br />
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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.
Code:
[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

New Member
Joined
Apr 23, 2015
Messages
19
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?

ItemOccurrence DateItemFirst306090
383849/13/201702/01/200506/21/2017225
456467/20/201712/20/202407/11/2017456
117209/14/201702/01/193206/05/201761123
446399/21/201703/19/202202/07/2017222
265539/15/201709/11/197209/15/2017111
117327/3/201702/13/193204/13/2017113
765779/25/201708/28/210909/25/2017222
117209/27/201711/28/207306/26/2017111

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

holzie

New Member
Joined
Apr 23, 2015
Messages
19
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.
 

Forum statistics

Threads
1,082,253
Messages
5,364,043
Members
400,776
Latest member
JimmyLee

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top