Counting occurences of same product ID within separate date ranges

T

tonyk22

New Member
Joined
Jul 17, 2017
Messages
5
I have data that shows me Product ID, Date appeared on report and quantity of product. I want to understand that if the product appeared on the report for X days (excluding weekends) then there was a break in the date trend (ie a weeks gap), then appeared again, how I can count the amount of times that item has appeared?

So the product may appear (repeat) on the report 10 times but the date trend has only happened once, twice or more occasions. This would show if I have a problem with a product continually being identified as "out of stock".

[TABLE="width: 211"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Item Code[/TD]
[TD]CurrentDte[/TD]
[TD]Σ In Market BO Qty[/TD]
[/TR]
[TR]
[TD="align: right"]7456[/TD]
[TD="align: right"]06/08/2018[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]7456[/TD]
[TD="align: right"]07/08/2018[/TD]
[TD="align: right"]581[/TD]
[/TR]
[TR]
[TD="align: right"]7456[/TD]
[TD="align: right"]08/08/2018[/TD]
[TD="align: right"]688[/TD]
[/TR]
[TR]
[TD="align: right"]7456[/TD]
[TD="align: right"]09/08/2018[/TD]
[TD="align: right"]641[/TD]
[/TR]
[TR]
[TD="align: right"]7456[/TD]
[TD="align: right"]10/08/2018[/TD]
[TD="align: right"]647[/TD]
[/TR]
[TR]
[TD="align: right"]7456[/TD]
[TD="align: right"]13/08/2018[/TD]
[TD="align: right"]674[/TD]
[/TR]
[TR]
[TD="align: right"]7456[/TD]
[TD="align: right"]14/08/2018[/TD]
[TD="align: right"]703[/TD]
[/TR]
[TR]
[TD="align: right"]7456[/TD]
[TD="align: right"]15/08/2018[/TD]
[TD="align: right"]730[/TD]
[/TR]
[TR]
[TD="align: right"]7456[/TD]
[TD="align: right"]16/08/2018[/TD]
[TD="align: right"]743[/TD]
[/TR]
[TR]
[TD="align: right"]7456[/TD]
[TD="align: right"]17/08/2018[/TD]
[TD="align: right"]765[/TD]
[/TR]
[TR]
[TD="align: right"]7456[/TD]
[TD="align: right"]31/08/2018[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]7456[/TD]
[TD="align: right"]03/09/2018[/TD]
[TD="align: right"]181[/TD]
[/TR]
[TR]
[TD="align: right"]7456[/TD]
[TD="align: right"]04/09/2018[/TD]
[TD="align: right"]265[/TD]
[/TR]
[TR]
[TD="align: right"]7456[/TD]
[TD="align: right"]05/09/2018[/TD]
[TD="align: right"]539[/TD]
[/TR]
[TR]
[TD="align: right"]7456[/TD]
[TD="align: right"]06/09/2018[/TD]
[TD="align: right"]601[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]06/08/2018[/TD]
[TD="align: right"]897[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]07/08/2018[/TD]
[TD="align: right"]1266[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]08/08/2018[/TD]
[TD="align: right"]2353[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]09/08/2018[/TD]
[TD="align: right"]1699[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]10/08/2018[/TD]
[TD="align: right"]3124[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]13/08/2018[/TD]
[TD="align: right"]3523[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]14/08/2018[/TD]
[TD="align: right"]3696[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]15/08/2018[/TD]
[TD="align: right"]3960[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]16/08/2018[/TD]
[TD="align: right"]3967[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]17/08/2018[/TD]
[TD="align: right"]4436[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]23/08/2018[/TD]
[TD="align: right"]167[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]29/08/2018[/TD]
[TD="align: right"]325[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]30/08/2018[/TD]
[TD="align: right"]2009[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]31/08/2018[/TD]
[TD="align: right"]2089[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]03/09/2018[/TD]
[TD="align: right"]2609[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]04/09/2018[/TD]
[TD="align: right"]3890[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]05/09/2018[/TD]
[TD="align: right"]4480[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]06/09/2018[/TD]
[TD="align: right"]4507[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]10/09/2018[/TD]
[TD="align: right"]4972[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]11/09/2018[/TD]
[TD="align: right"]5480[/TD]
[/TR]
[TR]
[TD="align: right"]7457[/TD]
[TD="align: right"]12/09/2018[/TD]
[TD="align: right"]5960[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Try this for data starting "A2" and results in columns "E & F".
Code: 
[COLOR="Navy"]Sub[/COLOR] MG15Sep38
[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] Q [COLOR="Navy"]As[/COLOR] Variant, Dt [COLOR="Navy"]As[/COLOR] Date, t
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[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]
        .Add Dn.Value, Array(Dn.Offset(, 1), 0)
    [COLOR="Navy"]Else[/COLOR]
        Q = .Item(Dn.Value)
            [COLOR="Navy"]If[/COLOR] DateDiff("d", Q(0), Dn.Offset(, 1).Value) > 1 [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]For[/COLOR] Dt = Q(0) To Dn.Offset(, 1).Value
                    [COLOR="Navy"]If[/COLOR] Not WeekdayName(Weekday(Dt, 4), True) = "Sat" And Not WeekdayName(Weekday(Dt, 4), True) = "Sun" [COLOR="Navy"]Then[/COLOR]
                        Q(1) = Q(1) + 1
                        t = WeekdayName(Weekday(Dt, 4), True)
                        [COLOR="Navy"]Exit[/COLOR] For
                    [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]Next[/COLOR] Dt
            [COLOR="Navy"]End[/COLOR] If
        Q(0) = Dn.Offset(, 1).Value
        .Item(Dn.Value) = Q
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] C [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Range("E1:F1") = Array("Item Code", "Count")
C = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .KEYS
    C = C + 1
    Cells(C, "e") = K
    Cells(C, "F") = .Item(K)(1)
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 168"]
<tbody>[TR]
[TD="width: 56, bgcolor: transparent"]Item Code
[/TD]
[TD="width: 73, bgcolor: transparent"]CurrentDte
[/TD]
[TD="width: 95, bgcolor: transparent"]Σ In Market BO Qty
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456
[/TD]
[TD="bgcolor: transparent, align: right"]10/08/2018
[/TD]
[TD="bgcolor: transparent, align: right"]647
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456
[/TD]
[TD="bgcolor: transparent, align: right"]13/08/2018
[/TD]
[TD="bgcolor: transparent, align: right"]674
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
MickG said:
Try this for data starting "A2" and results in columns "E & F".
Code: 
[COLOR=navy]Sub[/COLOR] MG15Sep38
[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] Q [COLOR=navy]As[/COLOR] Variant, Dt [COLOR=navy]As[/COLOR] Date, t
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[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]
        .Add Dn.Value, Array(Dn.Offset(, 1), 0)
    [COLOR=navy]Else[/COLOR]
        Q = .Item(Dn.Value)
            [COLOR=navy]If[/COLOR] DateDiff("d", Q(0), Dn.Offset(, 1).Value) > 1 [COLOR=navy]Then[/COLOR]
                [COLOR=navy]For[/COLOR] Dt = Q(0) To Dn.Offset(, 1).Value
                    [COLOR=navy]If[/COLOR] Not WeekdayName(Weekday(Dt, 4), True) = "Sat" And Not WeekdayName(Weekday(Dt, 4), True) = "Sun" [COLOR=navy]Then[/COLOR]
                        Q(1) = Q(1) + 1
                        t = WeekdayName(Weekday(Dt, 4), True)
                        [COLOR=navy]Exit[/COLOR] For
                    [COLOR=navy]End[/COLOR] If
                [COLOR=navy]Next[/COLOR] Dt
            [COLOR=navy]End[/COLOR] If
        Q(0) = Dn.Offset(, 1).Value
        .Item(Dn.Value) = Q
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
[COLOR=navy]Dim[/COLOR] C [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
Range("E1:F1") = Array("Item Code", "Count")
C = 1
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] .KEYS
    C = C + 1
    Cells(C, "e") = K
    Cells(C, "F") = .Item(K)(1)
[COLOR=navy]Next[/COLOR] K
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Click to expand...

Thank you for the code, I've entered into the data but it is returning too many counts. It would appear that if there is a break in entries due to weekend (last date a Friday, then next date a Monday), it's counting them as separate occurences vs just 1. As I am a novice and don't fully understand code, is there a simple fix to correct this? An example is my 1st stock code 7456, you can see the break in data entries due to the weekend but code is reading this as 2 occurences vs one.
 
Upvote 0
This is my results , what are yours ???
[TABLE="width: 129"]
<colgroup><col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3840;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 108, bgcolor: transparent"]Item Code[/TD]
[TD="width: 64, bgcolor: transparent"]Count[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7457[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

It may be the "Weekday" index is incorrect
Code: 
 t = WeekdayName(Weekday(Dt, 4), True)


From the line above Check that the "WeekdayName" is correct against the date variable "Dt".
If incorrect change the value "4" until you get the correct Day against the date variable "Dt".
Then adjust the "4" (twice) value in the code , 2 lines above.
 
Upvote 0
Thanks again Mike, not sure that corrects the issue.

[TABLE="width: 168"]
<colgroup><col width="56" style="width: 42pt;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 3114;"> <col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 4053;"> <tbody>[TR]
[TD="width: 56, bgcolor: transparent"]Item Code[/TD]
[TD="width: 73, bgcolor: transparent"]CurrentDte[/TD]
[TD="width: 95, bgcolor: transparent"]Σ In Market BO Qty[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456[/TD]
[TD="bgcolor: transparent, align: right"]06/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]1000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456[/TD]
[TD="bgcolor: transparent, align: right"]07/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]581[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456[/TD]
[TD="bgcolor: transparent, align: right"]08/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]688[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456[/TD]
[TD="bgcolor: transparent, align: right"]09/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]641[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456[/TD]
[TD="bgcolor: transparent, align: right"]10/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]647[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456[/TD]
[TD="bgcolor: transparent, align: right"]13/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]674[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456[/TD]
[TD="bgcolor: transparent, align: right"]14/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]703[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456[/TD]
[TD="bgcolor: transparent, align: right"]15/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]730[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456[/TD]
[TD="bgcolor: transparent, align: right"]16/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]743[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456[/TD]
[TD="bgcolor: transparent, align: right"]17/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]765[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456[/TD]
[TD="bgcolor: transparent, align: right"]31/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]44[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456[/TD]
[TD="bgcolor: transparent, align: right"]03/09/2018[/TD]
[TD="bgcolor: transparent, align: right"]181[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456[/TD]
[TD="bgcolor: transparent, align: right"]04/09/2018[/TD]
[TD="bgcolor: transparent, align: right"]265[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456[/TD]
[TD="bgcolor: transparent, align: right"]05/09/2018[/TD]
[TD="bgcolor: transparent, align: right"]539[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7456[/TD]
[TD="bgcolor: transparent, align: right"]06/09/2018[/TD]
[TD="bgcolor: transparent, align: right"]601[/TD]
[/TR]
</tbody>[/TABLE]

As you can see above, just taking the 1st product there are consecutive entries date wise from 06/08/2018 - 10/08/2018. The data then starts again from 13/08/2018. My understanding is the code is calculating the gap as a separate occurrence, however it is the same occurrence and the weekend dates are missing. How can I achieve my aim without adding date data into the source? The outcome in this example should be 2, not 3.
 
Upvote 0
I have since been shared a formula that seems to work.

=IF(A3=A2,IF(NETWORKDAYS.INTL(B2,B3)=2,0,1),1)
Thanks for helping
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Count how many times a product sold in a date range
Replies
3
Views
399
Peter_SSs
Peter_SSs
J
  • Question Question
Count out of date record
Replies
5
Views
696
Fluff
Fluff
K
  • Question Question
Extract data between two sets of dates in two columns.
Replies
4
Views
1K
RasGhul
RasGhul
T
  • Question Question
Multiple match
Replies
5
Views
616
Marcelo Branco
M
S
  • Question Question
CountIf Function for Variable Date Range
Replies
9
Views
4K
dreid1011
dreid1011

Forum statistics

Threads
1,223,893
Messages
6,175,249
Members
452,623
Latest member
Techenthusiast

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top